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 Viktar Karpach