comma separated values by column in sql

Hello All,

Sometimes we need comma separated values by columnName(e.g By Category, By ID, By Name etc) in sql query.

Generally we are making a user defined function for above problem. If we have large amount of data and there are multiple joins in query then it degrades the performance.

So how can we increase the performance of our query and how can we get comma separated values by columnName.

we can achieve this by using “FOR XML PATH“.

Lets take an example.

we have a table with column ID and Name.

Table contains below data

ID    Name
2    sachin
4    yuvraj
1    saurav
3    kohli
4    sharma
3    dravid
1    gambhir
3    raina
4    karthik
2    sehvag

Now we need to display it as

ID    Names
1     saurav, gambhir
2     sachin, sehvag
3     kohli, dravid, raina
4     yuvraj, sharma, karthik

see below query with temporary table variable.

    ID INT,
    name NVARCHAR(100)

INSERT INTO @tblTest VALUES(2,'sachin')
INSERT INTO @tblTest VALUES(4,'yuvraj')
INSERT INTO @tblTest VALUES(1,'saurav')
INSERT INTO @tblTest VALUES(3,'kohli')
INSERT INTO @tblTest VALUES(4,'sharma')
INSERT INTO @tblTest VALUES(3,'dravid')
INSERT INTO @tblTest VALUES(1,'gambhir')
INSERT INTO @tblTest VALUES(3,'raina')
INSERT INTO @tblTest VALUES(4,'karthik')
INSERT INTO @tblTest VALUES(2,'sehvag')

        (SELECT ', '+ 
        FROM @tblTest b  
        WHERE a.ID = b.ID 
        FOR XML PATH('')) 
    ) Names
FROM @tblTest a 

Hope this will help you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>