SQL 查询中的逗号分隔值 [英] Comma Separated values with SQL Query

问题描述

我的SQL表如下

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

这是 city_code 和 Post_Code 之间的映射.一个 City_Code 有许多邮政编码.现在我想运行一个查询来获得如下内容

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

你能帮我处理一下这个 SQL 表吗

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

推荐答案

试试这个:

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

这篇关于SQL 查询中的逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！