Assume I have a table such as following and I would like to select customers who purchased more than 1 item.

My final output should be something like

My code is

SELECT  
       customer
      ,item1
      ,item2
      ,item3
       item4
FROM StoreData
WHERE item1 IS NOT NULL OR 
      item2 IS NOT NULL OR
      item3 IS NOT NULL OR
      item4 IS NOT NULL

But this seems to be incorrect? Any suggestions? Many Thanks

CodePudding user response：

Try something like this:

WHERE 
(
 (CASE WHEN item1 IS NOT NULL THEN 1 ELSE 0 END) 
 (CASE WHEN item2 IS NOT NULL THEN 1 ELSE 0 END) 
 (CASE WHEN item3 IS NOT NULL THEN 1 ELSE 0 END) 
 (CASE WHEN item4 IS NOT NULL THEN 1 ELSE 0 END) 
) > 1

CodePudding user response：

You could try:

SELECT s.* 
from StoreData s 
inner join  (
               select customer,concat_ws('-',item1,item2,item3,item4) as orders
               from StoreData
             ) as t1 on t1.customer=s.customer
where CHARINDEX('-',orders ) >= 1;

Demo

CodePudding user response：

Another approach to this question is to use the "UNPIVOT" expression:

SELECT customer, COUNT(*) --, Item, Quantity
FROM dbo.StoreData
UNPIVOT  (
    Quantity FOR Item IN (item1, item2, item3, item4) 
) AS UnpivotTable
GROUP BY
    customer
HAVING
    COUNT(*) > 1

The UNPIVOT command helps you to see item1, item2, ..., itemN as single rows. Then you can apply a basic group by on it to check whether the count is greater the desired quantity or not.