how to display child record of the table in a single column as a CSV format in sqlserver

Description:-

In this example we explain that how to display child record of the table in a single column as a CSV format in sqlserver. Or how to display list of state under a country in a single column as a CSV format.

Sometime you have a real this type of requirement in your project in which you have to display data in grid like one column for country name and second column for state. But at that time in a single country there are multiple state under single country so better ways to display state is in single column separated by comma.

First check the data in your table like

select * from Country

select * from State

then how to display multiple state name in a single column by using following query.

SELECT

      [Country].[C_Id],[Country].[name],

      ISNULL(STUFF(

(

SELECT

', ' + [S_Name]

FROM

(

SELECT

[S_Name]

FROM

[DBO].[State]

WHERE

[State].[cid] = [Country].[C_Id]

)

AS [State] FOR XML PATH('')

)

,1,1,''),'') AS [List Of State name under country Seaprted by comma]

FROM

      [DBO].[Country]