逗号分隔值与 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
相关文章