pg多行合并为逗号分隔一行_sqlserver id相同的多行合并为一行，用逗号分隔

单表操作

创建测试表city：

把cityLevel相同的cityName合并在一起用逗号分隔 如：北京,上海,广州

SELECT cityLevel,

cityName =stuff(

(select ','+cityName FROM city WHERE cityLevel = c.cityLevel for xml path(''))

,1,1,'')

FROM city c

group by cityLevel

用city表和一张新的city命名为c,匹配cityLevel相同的项目,然后用cityLevel进行分组

两表操作

将city表拆开

citys:

city:

SELECT temp2.cityLevel,

cityName =stuff(

(select ','+cityName FROM

(select cs.cityLevel,cs.cityId,c.cityName from citys as cs inner join city as c on cs.cityId = c.id) temp

WHERE temp.cityLevel = temp2.cityLevel for xml path(''))

,1,1,'')

FROM (select cs.cityLevel,cs.cityId,c.cityName from citys as cs inner join city as c on cs.cityId = c.id) temp2

group by temp2.cityLevel