逗号分隔值与 SQL 查询

沃梦达教程数据库问题

2021-01-01 2

Comma Separated values with SQL Query(逗号分隔值与 SQL 查询)

本文介绍了逗号分隔值与 SQL 查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的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