How to get column values as comma separated list in sql server | Convert column values to row

Introduction: In previous articles articles i explained How to Split large string separated by comma in Sql Server and How to convert/downgrade SQL Server 2012,2008 database to SQL Server 2005 or lower version and How to Backup and restore sql server database and How to create Sql server database script and Create database from that script and Take automatic backup of Sql server Database.
 In this article I will explain how to get column values as comma separated list or we can say Convert/ Show the Column values of the table of the Sql server database and show as a comma separated string/row .

E.g If the Column has the value

1

2

3

4

5

Then on executing the below queries will return  1, 2, 3, 4, 5 or 1 2 3 4 5

Implementation: I have mentioned two sql queries below:

DECLARE @Str varchar(max)

SELECT @Str=COALESCE(@Str,'') + CAST(YourColumn as varchar(100)) + ','

FROM YourTable

SELECT @Str

The above query will return the values of a particular column in a row/list separated by comma.

 

DECLARE @Str varchar(max)

SELECT @Str=COALESCE(@Str,'') + CAST(YourColumn as varchar(100)) + ' '

FROM YourTable

SELECT @Str

The above query will return the values of a particular column in a row/list separated by a single space.

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post