Sql - Combining Field w/ Multiple Rows Into One Field in a Single Row Via Cursor

Sample Table:

ID             Product

1              Survey

1              Policy

1              Invoice

2              Invoice

2              Survey

3              Policy

3              Invoice

What I would like to end up with is a table w/ 3 rows (one for each of the ids) w/ the products in a comma separated list:

ID             Product

1              Survey, Policy, Invoice

2              Invoice, Survey

3              Policy, Invoice

I was able to do this w/ a User Defined Scalar Function, but it is very slow, I believe the more proper solution would to be a cursor, but that is where I need the help开发者_开发问答

Not sure about the speed vs UDF or cursor but you can give this a try:

with T(ID, Product) as 
(
select 1,              'Survey' union all
select 1,              'Policy' union all
select 1,              'Invoice' union all
select 2,              'Invoice' union all
select 2,              'Survey' union all
select 3,              'Policy' union all
select 3,              'Invoice'
)

select
  T1.ID,
  stuff((select ', '+T2.Product
         from T as T2 
         where T1.ID = T2.ID
         for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Product
from T as T1
group by T1.ID

Runnable Example: https://data.stackexchange.com/stackoverflow/q/101245/

DECLARE @tbl AS TABLE (ID int, Product VARCHAR(50))

INSERT INTO @tbl VALUES (
 1              ,'Survey'),
( 1              ,'Policy'),
( 1              ,'Invoice'),
( 2              ,'Invoice'),
( 2              ,'Survey'),
( 3              ,'Policy'),
( 3              ,'Invoice')

;WITH IDs AS (
SELECT ID
FROM @tbl
GROUP BY ID
)
SELECT IDs.ID, STUFF(Products.Products, 1, 1, '')
FROM IDs
OUTER APPLY (
SELECT ',' + Product
FROM @tbl AS tbl
WHERE ID = IDs.ID
FOR XML PATH ('')
) AS Products (Products)