0

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.

2
  • Is redesigning the table on option? This would be much easier with only 3 columns (dealerID, itemName, itemOnHand)
    – tpet
    Dec 8 '16 at 19:00
  • 1
    @tper - yes a redesign is an option. In my desired output I need only one row returned per dealerID Dec 8 '16 at 19:01
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
4
  • 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), Dec 8 '16 at 19:06
  • 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?
    – tpet
    Dec 8 '16 at 19:08
  • They are both equally important. I just want one row for each dealerID with all items returned on that one row. Dec 8 '16 at 19:09
  • Got it. Does the edited example using STUFF work for you?
    – tpet
    Dec 8 '16 at 19:26
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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.