Get table column value as comma separated value (CSV) in SQL

The most common problem to get the table column value as CSV(Comma separated value) in SQL and it gets more complex when we need to get it while getting information from multiple tables. So today i am going to elaborate how we can get the CSV value with other information. So for example we have two tables as shown below :

Table A                       Table B
IDA         NameA               IDB   NameB  IDA

1             Name1                    1    NameB1    3
2             Name2                    2    NameB2    3
3             Name3                    3    NameB3    4
4             Name4                    4    NameB4    4
5             Name5                    5    NameB5    4

There are two ways to get the value as comma separated (create same table on your test database and execute the following script as it is :

DECLARE @Names VARCHAR(8000)
select @Names= COALESCE(@Names + ‘, ‘, ”) + a.NameA
from tableA a join tableB b on a.IDA = b.IDA
print @Names
Output :
csv2

 

WITH CTE_CSV (NameA)
AS
(
SELECT SUBSTRING((SELECT ‘,’ + NameA FROM TableA
FOR XML PATH(”)),2,10000) AS CSV
)
select NameA from CTE_CSV
Output :
csv

My Expected output is:
NameA      NameB
Name3      NameB1, NameB2
Name4      NameB3, NameB4, NameB5

more code will be added soon…….

Advertisements
Get table column value as comma separated value (CSV) in SQL

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