Home > SQL Queries, SQL Server, T-SQL > Converting multiple record into a single comma seperated value

Converting multiple record into a single comma seperated value

My friend happend to ask me a question on “How to convert multiple records into a single delimited separated value?”.

To simplify the question let us consider a table “CUSTOMER” that contains records as given below.

CustomerNumber ItemCode
C1 Item1
C1 Item2
C1 ItemI3
C1 Item4
C1 Item5
C2 Item6
C2 Item7
C2 Item8

We need to get all the items purchased by a customer in a single record. THere should be a record for each customer. that is, The result of the query should be

customerNumber
Items
C1 item1,item2,item3,item4,item5
C2 item6,item7,item8

The first thing that comes to our mind is to use a cursor, while loop etc, and concatenate each record seperated by (,).   This would surely result in performance issue , as we would be executing the query against a table containing millions of records.

This is where I thought of using SQL XML. We can get the result by using a simple query and combining XML. The XML PATH can be used to achieve it. The query will look as given below. For more on XML Path refer http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

SELECT DISTINCT

CUSTNUM,

STUFF

(

(

SELECT

‘,’+ITEM

FROM CUSTOMER I WITH(NOLOCK)

WHERE C.CUSTNUM = I.CUSTNUM

FOR XML PATH()

)

,1,1,

)

FROM CUSTOMER C WITH(NOLOCK)

If there are any other better way to achieve the task,  I would be glad to hear that.

customerNumber Items
C1 item1
C1 item2
C1 item3
C1 item4
C1 item5
C2 item6
C2 item7
C2 item8
Advertisements
Categories: SQL Queries, SQL Server, T-SQL
  1. guru j
    June 15, 2009 at 2:16 am

    Thanks.. ;)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: