SQL Server : Displaying child records in a single column as CSV format in SQL Server

Yesterday, I was asked by one of my team member, how to display the child records as comma separated values in a single column.

I would like to share the query which I have suggested him as it is often required to display data as a single column,especially data from child tables.

Let us create some sample records for this example:

CREATE TABLE [DBO].[Country]
(
[CountryID] INT IDENTITY(1,1),
[CountryName] VARCHAR(100)
)
GO

CREATE TABLE [DBO].[State]
(
[StateID] INT IDENTITY(1,1),
[StateName] VARCHAR (100),
[CountryID] INT
)
GO

Also, will insert few records into these tables.

INSERT INTO [DBO].[Country] ([CountryName]) VALUES ('India'),('United States'),('Australia')
GO

INSERT INTO [DBO].[State] ([StateName],[CountryID]) VALUES ('Andhra Pradesh',1),('Tamil Nadu',1),('Maharashtra',1),('Kerala',1),('Karnataka',1)
GO

INSERT INTO [DBO].[State] ([StateName],[CountryID]) VALUES ('California',2),('New York',2),('Texas',2),('Washington',2)
GO

INSERT INTO [DBO].[State] ([StateName],[CountryID]) VALUES ('New South Wales',3),('Tasmania',3),('Victoria',3)
GO

First, let us execute the below queries and check the records :

SELECT [CountryID],[CountryName] FROM [DBO].[Country]
GO

SELECT [StateID],[StateName],[CountryID] FROM [DBO].[State]
GO

Hope, the above statements were executed successfully. J

Now, let's get to the real situation, suppose we need to display state names in CSV format for “India”.

The following query can be used to display the state names in a single column.

SELECT
ISNULL(STUFF(
(

SELECT

', ' + [StateName]
FROM
(
SELECT
[StateName]
FROM
[DBO].[State]
WHERE
[State].[CountryID] = 1
)
AS [State] FOR XML PATH('')
)
,1,1,''),'') AS [StateName]

GO

The output would be :

The above query satisfying only for single country right?
So, let us write a script to display all the state names for all the countries using correlated sub-query.

SELECT
      [Country].[CountryID],[Country].[CountryName],
      ISNULL(STUFF(
(
SELECT

', ' + [StateName]
FROM
(
SELECT
[StateName]
FROM
[DBO].[State]
WHERE
[State].[CountryID] = [Country].[CountryID]
)
AS [State] FOR XML PATH('')
)
,1,1,''),'') AS [StateName]
FROM
      [DBO].[Country]
GO

The output would be :

I hope, this article may have helped you to understand, how to display the records in CSV format and also you can find more.

Note:

The above queries will work only in SQL Server 2005 and above versions as 'FOR XML PATH' was introduced in SQL Server 2005.