我的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有很多邮政编码.现在我想运行一个查询来获得以下内容
City_Code Post_Code Post_Code_Description 100 A1,C8,Z3 ABC,XYZ,MNO 200 D4 LMN 300 E3,B9 IJK,RST
可以请你帮忙我这个sql表如下
解决方法
尝试这个：
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('')),'')
FROM your_table a
GROUP BY City_Code
