postgresql 循环拼接多行数据 成一行

sql：

select array_to_string(array(select concat('when provinceNumber=',province_number,' then provinceName=','''',province_name,'''') from (select distinct province_number,province_name from 表A order by province_number ) a),';')

注意点：

1.''''是用来拼接'这个引号的。

2.array_to_string  和  array这两个函数不太会用。

结果如下：

when provinceNumber=1 then provinceName='北京';when provinceNumber=2 then provinceName='天津';when provinceNumber=3 then provinceName='河北';when provinceNumber=4 then provinceName='山西';when provinceNumber=5 then provinceName='内蒙古';when provinceNumber=6 then provinceName='辽宁';when provinceNumber=7 then provinceName='吉林';when provinceNumber=8 then provinceName='黑龙江';when provinceNumber=9 then provinceName='上海';when provinceNumber=10 then provinceName='江苏';when provinceNumber=11 then provinceName='浙江';when provinceNumber=12 then provinceName='安徽';when provinceNumber=13 then provinceName='福建';when provinceNumber=14 then provinceName='江西';when provinceNumber=15 then provinceName='山东';when provinceNumber=16 then provinceName='河南';when provinceNumber=17 then provinceName='湖北';when provinceNumber=18 then provinceName='湖南';when provinceNumber=19 then provinceName='广东';when provinceNumber=20 then provinceName='广西';when provinceNumber=21 then provinceName='海南';when provinceNumber=22 then provinceName='重庆';when provinceNumber=23 then provinceName='四川';when provinceNumber=24 then provinceName='贵州';when provinceNumber=25 then provinceName='云南';when provinceNumber=26 then provinceName='西藏';when provinceNumber=27 then provinceName='陕西';when provinceNumber=28 then provinceName='甘肃';when provinceNumber=29 then provinceName='青海';when provinceNumber=30 then provinceName='宁夏';when provinceNumber=31 then provinceName='新疆';

 

数据截图如下：