Comma Separated values with SQL Query

Question

My SQL table is like following

City_Code     Post_Code    Post_Code_Description
100           A1           ABC
100           C8           XYZ
100           Z3           MNO
200           D4           LMN
300           E3           IJK
300           B9           RST

It's a mapping between city_code and Post_Code. One City_Code has many Post Codes. Now i want to run a Query to get something like following

City_Code     Post_Code    Post_Code_Description
100           A1,C8,Z3     ABC,XYZ,MNO
200           D4           LMN
300           E3,B9        IJK,RST

Can you please help me with thisy SQL table is like following

First sentence "My SQL table is like following" but the space between "My" and "SQL" hmm... Now I'm not sure, I removed tag :) — Robert, Oct 01 '12 at 10:05
i imagine the answer is exactly the same as your previous question [Comma-separated values with SQL query](http://stackoverflow.com/questions/12670409/comma-separated-values-with-sql-query) — podiluska, Oct 01 '12 at 10:07

score 26 · Accepted Answer · answered Oct 01 '12 at 10:14

26

try this:

SELECT City_Code, 
      Post_Code = 
        STUFF((SELECT ', ' + Post_Code
           FROM your_table b 
           WHERE b.City_Code = a.City_Code 
          FOR XML PATH('')), 1, 2, ''),
      Post_Code_Description=
        STUFF((SELECT ', ' + Post_Code_Description
           FROM your_table b 
           WHERE b.City_Code = a.City_Code 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY City_Code

answered Oct 01 '12 at 10:14

Joe G Joseph

23,518
5
56
58

1

I believe it should be stuff(..., 1, 1, '') else losing a character. – crokusek Feb 25 '14 at 21:02
1

@crokusek No. The SELECT prefixes with 2 characters and so the STUFF needs to remove 2 characters from position 1. You'd be right if it was just adding a comma. – StarNamer Aug 15 '18 at 10:55

Taryn · Answer 2 · 2012-10-01T12:04:44.743

11

If you are using MySQL you can use GROUP_CONCAT()

select City_Code, 
     GROUP_CONCAT(Post_Code) Post_Code, 
     GROUP_CONCAT(Post_Code_Description) post_code_description
from yourtable
group by City_Code

For SQL Server you can use STUFF() and FOR XML PATH()

select city_code,
    Stuff((SELECT ', ' + post_code 
            FROM yourtable t2
            where t1.city_code = t2.city_code
            FOR XML path('')),1,1,'') Post_Code,
    Stuff((SELECT ', ' + post_code_description
            FROM yourtable t2
            where t1.city_code = t2.city_code
            FOR XML path('')),1,1,'') post_code_description
from yourtable t1
group by city_code

edited Oct 01 '12 at 12:04

answered Oct 01 '12 at 10:00

Taryn

242,637
56
362
405

I am using SQL Server 2008 R2 – InTheWorldOfCodingApplications Oct 01 '12 at 10:08
@user1711287 please see my edit, I added an answer for SQL Server – Taryn Oct 01 '12 at 10:12
3

Bit odd to say that for SQL Server you can use `STUFF`. It is `XML PATH` that does all the work. `STUFF` just sneaks in at the end and removes the surplus comma. – Martin Smith Oct 01 '12 at 10:18
@MartinSmith you are correct, updated to include link to `FOR XML PATH()` – Taryn Oct 01 '12 at 10:19
nice answer :) btw, any reason why you removed all the tag in the questions `MySQL-Pivot`? anything wrong with creating new tag? – John Woo Oct 01 '12 at 11:12
@JohnWoo nothing wrong with creating a new tag but it seems redundant considering most questions are tagged with `MySQL` and `PIVOT`, I don't think there is a need for `MySQL-PIVOT`. Most `PIVOT` question even `MySQL` ones can fit in the normal `PIVOT` tag, I think adding one will just be confusing. – Taryn Oct 01 '12 at 11:16

score 4 · Answer 3 · answered Oct 01 '12 at 10:10

try this:

select city_code,substring((select ',' + post_code  
from city b where a.city_code=b.city_code
for xml path('')
),2,100)as post_code,
substring((select ',' + post_code_description 
from city c where a.city_code=c.city_code
for xml path('')
),2,100)as post_code_description
from city a
group by a.city_code

score 1 · Answer 4 · answered Oct 03 '12 at 09:50

Use a recursive query for this:

--Prepare Dummy Data
;WITH CITIES 
     AS (SELECT 100   AS City_Code, 
                'A1'  AS Post_code, 
                'ABC' AS Post_Code_Description 
         UNION 
         SELECT 100   AS City_Code, 
                'C8'  AS Post_code, 
                'XYZ' AS Post_Code_Description 
         UNION 
         SELECT 100   AS City_Code, 
                'Z3'  AS Post_code, 
                'MNO' AS Post_Code_Description 
         UNION 
         SELECT 200   AS City_Code, 
                'D4'  AS Post_code, 
                'LMN' AS Post_Code_Description 
         UNION 
         SELECT 300   AS City_Code, 
                'E3'  AS Post_code, 
                'IJK' AS Post_Code_Description 
         UNION 
         SELECT 300   AS City_Code, 
                'B9'  AS Post_code, 
                'RST' AS Post_Code_Description), 
--Add Row numbers to each row
     PREPARE 
     AS (SELECT *, 
                ROW_NUMBER () 
                  OVER ( 
                    PARTITION BY CITY_CODE 
                    ORDER BY CITY_CODE) RN 
         FROM   CITIES),
--Start Recursive CTE 
     RECURSIVE 
     AS (
--Anchor Query
         SELECT CITY_CODE, 
                CAST(POST_CODE AS VARCHAR(MAX))             Post_code, 
                CAST(POST_CODE_DESCRIPTION AS VARCHAR(MAX)) 
                Post_Code_Description, 
                1                                           AS LEVEL, 
                RN 
         FROM   PREPARE 
         WHERE  RN = 1 
         UNION ALL 
--Recursive Query
         SELECT T1.CITY_CODE, 
                T1.POST_CODE + ',' + T2.POST_CODE, 
                T1.POST_CODE_DESCRIPTION + ',' 
                + T2.POST_CODE_DESCRIPTION, 
                T2.LEVEL + 1, 
                T1.RN 
         FROM   PREPARE AS T1 
                INNER JOIN RECURSIVE AS T2 
                        ON T1.RN = T2.RN + 1 
                           AND T1.CITY_CODE = T2.CITY_CODE) 
--Final Results
SELECT T1.CITY_CODE, 
       T1.POST_CODE, 
       T1.POST_CODE_DESCRIPTION 
FROM   RECURSIVE T1 
       INNER JOIN (SELECT CITY_CODE, 
                          COUNT(*) cnt 
                   FROM   CITIES 
                   GROUP  BY CITY_CODE)T2 
               ON T1.CITY_CODE = T2.CITY_CODE 
WHERE  T1.LEVEL = T2.CNT

Comma Separated values with SQL Query

4 Answers4

Linked

Related