KARPACH

WEB DEVELOPER BLOG

How to create comma separated list in T-SQL?

Twice this month I needed a comma-separated list created in T-SQL, so I decided to blog about this. You can use a short comma-separated list for AJAX details lists. For example, you have a list of users and you want to show quick details about selected user orders.

CustomerId FirstName LastName
1 Viktar Karpach
2 Joe Doe
3 Jane Forest

OrderId CustomerId Amount OderDate
1 1 119.12 10/12/2009
2 1 144.98 1/12/2009
3 1 76.12 8/5/2009
4 2 14.14 9/9/2009
5 3 17.89 10/10/2009

Following T-SQL code would build a list of customers and corresponding orders:

SELECT   c.FirstName + ' ' +c.LastName as [Customer Name],
         [Order Ids] = substring((SELECT ( ',' + cast(OrderId as varchar))
                       FROM [Order] ord
                            WHERE (ord.CustomerId = c.CustomerId)
                            ORDER BY ord.OrderId
                       FOR XML PATH( '' )
                      ), 2, 1000 )
FROM     Customer c

Result:

Viktar Karpach 1,2,3
Joe Doe 4
Jane Forest 4

Usually, a simple ids list is not sufficient enough, what if you need some more information?

Let’s pass Amount column as well:

SELECT   c.FirstName + ' ' +c.LastName as [Customer Name],
         [Order Ids] = substring((SELECT ( ',' + cast(OrderId as varchar) + ':' + cast(Amount as varchar))
                       FROM [Order] ord
                            WHERE (ord.CustomerId = c.CustomerId)
                            ORDER BY ord.OrderId
                       FOR XML PATH( '' )
                      ), 2, 1000 )
FROM     Customer c

Result:

Viktar Karpach 1:119.12,2:144.98,3:76.12
Joe Doe 4:14.14
Jane Forest 5:17.89

You can use JQuery to easily parse those results.

Posted on November 9, 2009 by