Return Only Rows where count >= 1

Posted on

Question :

This is my data set-up, I am needing a way to return only itemsnames and itemonhand where the count of itemonhand is >= 1

I tried simply adding an OR statement to the query, but that is returning items that have a 0 count.

  Declare @TableTest Table (dealerID int, itemname varchar(100), itemonhand int, itemname2 varchar(100), itemonhand2 int, itemname3 varchar(100), itemonhand3 int)
  INSERT INTO @TableTest (dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3) VALUES
  (1, 'ball', 0, 'hat', 1, 'sock', 0)
  ,(2, 'ball', 0, 'hat', 0, 'sock', 1)

  Select * FROM @TableTest
  where itemonhand2 = '1'
  OR itemonhand3 = '1'

My desired result set is

1  hat  1
2  sock 1

I just want one row for each dealerID with all items returned on that one row.

Answer :

This returns your results and is more extensible for more items. It has been edited to return multiple items per dealer.

DECLARE @TableTest Table (dealerID int, itemname varchar(100), itemonhand int)
INSERT INTO @TableTest (dealerID, itemname, itemonhand) VALUES
(1,'ball',0),
(1,'hat',1),
(1,'sock',0),
(1,'bees',1),
(2,'ball',0),
(2,'hat',0),
(2,'sock',1);


SELECT dealerID,
stock = STUFF (
            (SELECT ',' + itemname  FROM @TableTest as t1 WHERE t1.itemonhand = 1 and t1.dealerID = t2.dealerID FOR XML PATH ('')), 1,1, '' 
            )
FROM @TableTest as t2
GROUP BY dealerID

Live by the cross apply, die by the cross apply

 CREATE TABLE #TableTest
 (
   dealerID INT,
   itemname VARCHAR(100),
   itemonhand INT,
   itemname2 VARCHAR(100),
   itemonhand2 INT,
   itemname3 VARCHAR(100),
   itemonhand3 INT
 );
 INSERT INTO #TableTest
        ( dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3 )
 VALUES ( 1, 'ball', 0, 'hat', 1, 'sock', 0 )
  ,     ( 2, 'ball', 0, 'hat', 0, 'sock', 1 );


 SELECT tt.dealerID,
        ca.*
 FROM   #TableTest AS tt
 CROSS APPLY ( SELECT   *
               FROM     ( VALUES ( itemname, itemonhand), ( itemname2, itemonhand2), ( itemname3, itemonhand3) ) x ( item_name, item_count ) ) ca
 WHERE  ca.item_count = 1;

Leave a Reply

Your email address will not be published. Required fields are marked *