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:
 

6 comments :

  1. This comment has been removed by the author.

    ReplyDelete
  2. very good article

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

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

    ReplyDelete