Twice this month I needed comma separated lists build in T-SQL, so I decided to blog about this. You can use short comma separated lists for AJAX details lists. For example, you have 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 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 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.