0
select id, productid, optionid 
from tableA
where productid = 1

result (id, productid, optionid)

1 1 1
2 1 2
3 1 5

I would like the result to be (productid, optionids):

1 1,2,5

Naturally, I would think the query below should produce the above result

select productid, optionid 
from tableA
group by productid

But what function do I put optionid in?

dotnetN00b
  • 5,021
  • 13
  • 62
  • 95

1 Answers1

1

You can use FOR XML PATH (with some help from STUFF function) to do that. You cannot group as there's sadly no aggregate function for concatenating strings.

select distinct a.productid,
stuff((select ','+cast(s.optionid as varchar(10))
       from tableA s 
       where s.productid = a.productid 
       for XML path('')),1,1,'')
from tableA a

SQL Fiddle demo

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • 1
    +1 to the answer, but please don't say it like the `STUFF` function is doing the concatenation, since it's simply removing the extra comma. It's the `XML PATH` which makes this possible. – Lamak Dec 09 '13 at 20:13
  • Very true, I'll update my answer. Thanks a lot! – Szymon Dec 09 '13 at 20:15