Format data in sql server

Question

This is my SQL Server table data

            id  Name
            1   Active
            2    On-Hold
            3   Closed
            4    Cancelled
            5   Active
            6   On-Hold
            7   Closed
            8   Cancelled
            9   Active
            10  On-Hold
            11  Closed
            12  Cancelled

How can I query the table so that the results look like:

        Name        Id 
        Active     1,5,9
        On-Hold    2,6,10
        Closed     3,7,11
        Cancelled  4,8,12

possible duplicate of [sql server sub query with a comma separated resultset](http://stackoverflow.com/questions/8302337/sql-server-sub-query-with-a-comma-separated-resultset) — NickyvV, Feb 27 '14 at 11:36

vhadalgi · Answer 1 · 2014-02-27T12:45:46.003

drop table #t
create table #t(id int,name varchar(20))
insert into #t values(1,'Active'),
            (2,'On-Hold'),
            (3 ,'Closed'),
            (4,'Cancelled'),
            (5,'Active'),
            (6,'On-Hold'),
            (7,'Closed'),
            (8,'Cancelled'),
            (9,'Active'),
            (10,'On-Hold'),
            (11,'Closed'),
            (12,'Cancelled')

 select distinct name,
stuff(
(
 select ',' + cast(id as varchar(10)) from #t t1 where t1.name=t2.name for xml path(''),type).value('.','varchar(max)'),1,1,'') from #t t2

 group by t2.name,t2.id

#SEE DEMO

Ketan Panchal · Answer 2 · 2014-02-27T12:32:36.100

1

SELECT
   t1.name,
   MemberList = substring((SELECT ( ', ' + CAST(id as varchar) )
                           FROM table t2
                           WHERE t1.name = t2.name
                           ORDER BY 
                              name
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM table t1
GROUP BY name

edited Feb 27 '14 at 12:32

answered Feb 27 '14 at 11:47

Ketan Panchal

230
1
2
9

you will have trouble string concatenating an int – jenson-button-event Feb 27 '14 at 12:29
Yes in example i used it as varchar, i need to cast it to varchar. – Ketan Panchal Feb 27 '14 at 12:33

jenson-button-event · Accepted Answer · 2014-02-27T12:27:53.050

0

Use XML PATH

SELECT distinct t1.Name, 
  SUBSTRING((
    SELECT ',' + cast(t2.Id as varchar(30))
    FROM Status t2
    WHERE t2.Name = t1.Name
    FOR XML PATH('')), 2, 1000000) as [Id List]
FROM Status t1

Just ensure you swap your real table name in

edited Feb 27 '14 at 12:27

answered Feb 27 '14 at 11:41

jenson-button-event

18,101
11
89
155

You have to decode, also, the XML reserved chars. Otherwise, these chars will remain encoded: ...for xml path(''), type).value('.', 'varchar(max)') (or nvarchar(max)). – vhadalgi Feb 27 '14 at 12:43

Format data in sql server

3 Answers3