Can I get comma separated values from sub query? If not, how to get this done?

Question

I have a table

Create table Country_State_Mapping
(
    Country nvarchar(max),
    State nvarchar(max)
)

With 5 records.

Insert into Country_State_Mapping values('INDIA', 'Maharastra')
Insert into Country_State_Mapping values('INDIA', 'Bengal')
Insert into Country_State_Mapping values('INDIA', 'Karnatak')
Insert into Country_State_Mapping values('USA', 'Alaska')
Insert into Country_State_Mapping values('USA', 'California')

I need to write a SQL query which will have give me 2 records/2 columns as below.

1st column Contry and second AllStates

1 record(2 columns) will be

India and Maharastra,Bengal,Karnatak

2nd

USA and Alaska,California

I tried I like this

select distinct
    OutTable.Country,
    (select State
     from Country_State_Mapping InnerTable
     where InnerTable.Country = OutTable.Country)
from Country_State_Mapping AS OutTable

but did not work...

possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — juergen d, Jul 03 '12 at 12:53
The functionality you're looking for is a GROUP BY concatenation. MySQL has this built in, but MS-SQL does not. There have been other Stack Overflow articles on this, such as: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Richthofen, Jul 03 '12 at 12:57
Refer this http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/ — Madhivanan, Jul 03 '12 at 12:58

Kevin Aenmey · Accepted Answer · 2012-07-03T13:37:12.630

14

SELECT Country, AllStates = 
    STUFF((SELECT ', ' + State
           FROM Country_State_Mapping b 
           WHERE b.Country = a.Country
           FOR XML PATH('')), 1, 2, '')
FROM Country_State_Mapping a
GROUP BY Country

edited Jul 03 '12 at 13:37

answered Jul 03 '12 at 13:01

Kevin Aenmey

13,259
5
46
45

score 1 · Answer 2 · answered Jul 03 '12 at 13:21

This is a bit nasty and potentially slow if the number of records in the Country_State_Mapping table is large but it does get the job done. It uses the recursive feature of Common Table Expressions introduced in SQL 2005.

;WITH Base
AS
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country, [State] DESC) AS CountryRowId,
        ROW_NUMBER() OVER (ORDER BY Country, [State]) AS RowId,
        Country,
        [State]
    FROM Country_State_Mapping
),
Recur
AS
(
    SELECT
        CountryRowId,
        RowId,
        Country,
        [State]
    FROM Base
    WHERE RowId = 1

    UNION ALL

    SELECT
        B.CountryRowId,
        B.RowId,
        B.Country,
        CASE WHEN R.Country <> B.Country THEN B.[State] ELSE R.[State] + ',' + B.[State] END
    FROM Recur R
    INNER JOIN Base B
        ON R.RowId + 1 = B.RowId
)

SELECT *
FROM Recur
WHERE CountryRowId = 1
OPTION (MAXRECURSION 0)--Dangerous

You're seriously anchoring your CTE on a single row rather than all beginning rows for all countries at once? OUCHIE! — ErikE, Jul 13 '12 at 04:13

Can I get comma separated values from sub query? If not, how to get this done?

2 Answers2