Show Grouped By Items in Comma Separated Format

Question

I have a table like below:

create table Location
(
ContinentID int not null,
CountryID int not null,
StateCode nvarchar(10) not null
)

Insert into Location Values (1, 1, 'AP')
Insert into Location Values (1, 1, 'WB')
Insert into Location Values (1, 1, 'MH')
Insert into Location Values (1, 2, 'KA')
Insert into Location Values (1, 2, 'ID')
Insert into Location Values (3, 1, 'NY')
Insert into Location Values (3, 1, 'WA')
Insert into Location Values (3, 2, 'VI')

Here I need all the state codes should be shown in a comma separated format based on ContinentID and CountryID. So the output must look like below:

ContinentID     CountryID    StateCodes
-----------     ---------    ----------
1               1            AP,WB,MH
1               2            KA,ID
3               1            NY,WA
3               2            VI

I don't have much idea about SQL queries, I tried one below, but it didn't work:

SELECT Continentid, CountryID, CONCAT(StateCode, ',') FROM Location
GROUP BY Continentid, CountryID

How can I get the desired output using a single SQL Query ? Any help is appreciated.

score 1 · Answer 1 · answered Jan 14 '14 at 07:40

Ah -- it's tricky :) Here is one of the methods from here adapted for your data structure. I have verified that it produces the result you want except for a trailing comma...

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

  SELECT p1.ContinentID, p1.CountryID,
   ( SELECT StateCode + ','
       FROM Location p2
      WHERE p2.ContinentID = p1.ContinentID AND p2.CountryID = p1.CountryID
      ORDER BY StateCode
        FOR XML PATH('') ) AS StateCodes
  FROM Location p1
  GROUP BY ContinentID, CountryID

score 1 · Accepted Answer · edited May 23 '17 at 11:57

In T-SQL, FOR XML PATH probably gives you the best performance. STUFF handles the out of place leading comma.

Click here to see the SQL Fiddle.

SELECT ContinentID, CountryID,
      StateCode = 
        STUFF((SELECT ', ' + StateCode
           FROM Location b 
           WHERE b.ContinentID = a.ContinentID 
               and 
               b.CountryID = a.CountryID 
          FOR XML PATH('')), 1, 2, '')
FROM Location a
GROUP BY ContinentID, CountryID

This answer can also help.

Show Grouped By Items in Comma Separated Format

2 Answers2