sqlsql-serversql-server-2008t-sql

T-SQL - Projecting multiple records into one row for each key


A table can have multiple records for each key as below.

Actual Table:

Key         Value
----------- ---------
2149        805501   
2149        800936   
15385       800622   
18105       997057   
18105       999390   

Expected result:

Key         Value
----------- ---------
2149         805501,800936
15385       800622   
18105       997057,999390   

How can I solve this?


Solution

  • You should use FOR XML PATH:

    Select Key, STUFF((SELECT ',' + VALUE
                       FROM Table AS T2 
                       WHERE T1.Key = T2.Key
                      FOR XML PATH('')), 1, 1, '') 
    FROM Table T1
    GROUP BY Key
    

    UPDATE 1

    based on your comments

    enter image description here