Search This Blog

Friday, November 4, 2011

Sql Server: Convert table Column data into comma separated string or row


SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields




Here  I am using COALESCE function Sql Server 2005/2008 to convert a field into comma separated value of string or row
My data in the table looks like :
 

The result I want to show is like :




Select query will be:

Assuming my table name is  #test, It has 2 column field1,field2

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ',' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

If you wish select all the rows of field2 as comma separated list then query will be:


SELECT STUFF( -- Remove first comma
    (
        SELECT  ', ' + field2 FROM -- create comma separated values
        (
          SELECT field2 FROM #test --Your query here
        ) AS T FOR XML PATH('')
    )
    ,1,1,'') AS field2

OR

DECLARE @test NVARCHAR(max)  
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2= @test 


Output:
 

10 comments :

  1. This comment has been removed by the author.

    ReplyDelete
  2. I am not understanding what you have done in this peace of code......

    ReplyDelete
  3. Can we Declare variable in sql server 2008 ......iam getting error cannot open datbase..contact administrator or enable the external database

    ReplyDelete
  4. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing. sql server tutorial and sql server online course.

    ReplyDelete
  5. wow what is this really? Why aren't you doing this now? I think it's so awesome and awesome I have to share this with my friends and my son and wife 출장아로마
    출장아로마
    출장아로마
    출장아로마
    광주출장아로마right now I feel like I found an oasis in the desert Thank you so much for finding your site.

    ReplyDelete