How to create comma separated list in T-SQL?

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.


Posted on Monday, November 9, 2009 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5