Get Table Column Value as Comma Separated Value (CSV) in SQL

Introduction

The most common problem is 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.

Using the Code

So for example, we have two tables as shown below. Create two tables by using the following SQL scripts.

CREATE TABLE [dbo].[TableA](
    [IDA] [int] IDENTITY(1,1) NOT NULL,
    [NameA] [varchar](50) NULL,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
    [IDA] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[TableB](
	[IDB] [int] IDENTITY(1,1) NOT NULL,
	[NameB] [varchar](50) NULL,
	[IDA] [nchar](10) NULL,
 CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED 
(
	[IDB] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Use the below data scripts to add data in these above tables, it is just for the ease, otherwise you can have your own data values as well.

SET IDENTITY_INSERT [dbo].[TableB] ON
INSERT [dbo].[TableB] ([IDB], [NameB], [IDA]) VALUES (1, N'NameB1', N'3         ')
INSERT [dbo].[TableB] ([IDB], [NameB], [IDA]) VALUES (2, N'NameB2', N'3         ')
INSERT [dbo].[TableB] ([IDB], [NameB], [IDA]) VALUES (3, N'NameB3', N'4         ')
INSERT [dbo].[TableB] ([IDB], [NameB], [IDA]) VALUES (4, N'NameB3', N'4         ')
INSERT [dbo].[TableB] ([IDB], [NameB], [IDA]) VALUES (5, N'NameB4', N'4         ')
SET IDENTITY_INSERT [dbo].[TableB] OFF
/****** Object:  Table [dbo].[TableA]    Script Date: 03/09/2016 14:45:56 ******/
SET IDENTITY_INSERT [dbo].[TableA] ON
INSERT [dbo].[TableA] ([IDA], [NameA]) VALUES (1, N'Name1')
INSERT [dbo].[TableA] ([IDA], [NameA]) VALUES (2, N'Name2')
INSERT [dbo].[TableA] ([IDA], [NameA]) VALUES (3, N'Name3')
INSERT [dbo].[TableA] ([IDA], [NameA]) VALUES (4, N'Name4')
INSERT [dbo].[TableA] ([IDA], [NameA]) VALUES (5, N'Name5')
SET IDENTITY_INSERT [dbo].[TableA] OFF

Then the final output will be like below:

Table A IDA	NameA	Table B 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 very common ways to get the value as comma separated:

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

Hope this small tip with a simple approach will help others.