distinct values/comma separated array from a case statement

Question

I am building a dataset to use for an ssrs parameter and I am having trouble building distinct values that result out of a case statement...i also want a comma separated array based out of result sets... small sample below..

Sample Data:

itemcode     itemkey
1            1001
4            1002
5            1003
4            1004
7            1005
4            1006
8            1007
6            1008
5            1009

I do the following SELECT:

SELECT DISTINCT itemcode,
,CASE WHEN itemcode IN(1,7,8) THEN 'Green'
      WHEN itemcode IN(4,5) THEN 'Red'
      WHEN itemcode IN(6) THEN 'Blue'
      ELSE 'itemcode' 
      END AS 'Color'
FROM itemtable

returns this:

itemcode     Color
1            Green
4            Red
5            Red
6            Blue
7            Green
8            Green

I want to be able to SELECT DISTINCT Color (which is the case statement column) but also put the itemcodes in comma separated values.. so I would want these results (order by Color):

itemcode     Color
6            Blue
1,7,8        Green
4,5          Red

this way I can use this dataset as my "Available Values" in the ssrs parameter, with Value = itemcode and Label = Color

hope this makes sense... I can clarify if needed. TIA

thanks for posting your requirements. I don't see an attempt of the csv bit....? — Mitch Wheat, Dec 16 '14 at 23:44
Which RDBMS are you using? Please add the appropriate tag to your question. — Bohemian, Dec 16 '14 at 23:45
possible duplicate of [Concatenate one field after GROUP BY](http://stackoverflow.com/questions/13647394/concatenate-one-field-after-group-by) — Chris Latta, Dec 16 '14 at 23:51

score 0 · Answer 1 · edited May 23 '17 at 12:28

NOTE: this is a duplicate posting.
SQL Server : GROUP BY clause to get comma-separated values
SQL group_concat function in SQL Server

BUT here is the code.

DECLARE @Items TABLE
(
  itemcode int, 
  itemkey varchar(4)
)

INSERT INTO @Items (itemcode, itemkey)
select 1, '1001'
union select 2, '1002'
union select 1, '1001'
union select 4, '1002'
union select 5, '1003'
union select 4, '1004'
union select 7, '1005'
union select 4, '1006'
union select 8, '1007'
union select 6, '1008'
union select 5, '1009'


select * from @Items

DECLARE @ItemsGrouped TABLE
(
  itemcode varchar(10), 
  color varchar(10)
)

insert into @ItemsGrouped
SELECT DISTINCT itemcode,
CASE WHEN itemcode IN(1,7,8) THEN 'Green'
      WHEN itemcode IN(4,5) THEN 'Red'
      WHEN itemcode IN(6) THEN 'Blue'
      ELSE 'itemcode' 
      END AS 'Color'
FROM @Items 


SELECT color, Email = 
    STUFF((SELECT ', ' + itemcode
           FROM @ItemsGrouped b 
           WHERE b.color = a.color 
          FOR XML PATH('')), 1, 2, '')
FROM @ItemsGrouped a
GROUP BY color

distinct values/comma separated array from a case statement

1 Answers1