Return Only Rows where count >= 1

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.

• tpet: Is redesigning the table on option? This would be much easier with only 3 columns (dealerID, itemName, itemOnHand)
• SmallFries BigGuys: @tper - yes a redesign is an option. In my desired output I need only one row returned per dealerID

Answer 1

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

• SmallFries BigGuys: How would I return only one row per dealerID if the DDL has a case where a dealerID has multiple itemsonhand? Like if this was thrown into the DDL (1,'bees', 1),
• tpet: How would you like to determine which row to return for dealerID = 1 if they have both bees and hat with itemsonhand = 1? Are bees more important than hats?
• SmallFries BigGuys: They are both equally important. I just want one row for each dealerID with all items returned on that one row.
• tpet: Got it. Does the edited example using STUFF work for you?

Answer 2

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;