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

Answer 1

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

Answer 2

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