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;