postgre数据库如何实现行转列

显示全部楼层 · 2021-1-27 06:42:17

createtablet(daydate,equipmentvarchar(20),outputinteger);
insertintotvalues('2010-04-01','DAT501',100);
insertintotvalues('2010-04-01','DAT502',120);
insertintotvalues('2010-04-01','DAT503',130);
insertintotvalues('2010-04-02','DAT501',110);
insertintotvalues('2010-04-02','DAT502',105);
insertintotvalues('2010-04-03','DAT503',125);
insertintotvalues('2010-04-04','DAT501',100);
insertintotvalues('2010-04-04','DAT503',200);
--想得到如下结果
day|dat501|dat502|dat503
----------------+-----------+----------+--------
2010-04-01|100|120|130
2010-04-02|110|105|
2010-04-03

千问 · 2021-1-27 06:42:17

125
2010-04-04|100||200
分 -->

千问 · 2021-1-27 06:42:17

这个应该只能通过关联的方式解决：
selectt.day,t1.equipmentDAT501,t2.equipmentDAT502,t3.equipmentDAT503
from(selectdistinctdayfromt)t
leftjointt1
ont.day=t1.day
andt1.equipment='DAT501'
leftjointt2
ont.day=t2.day
andt2.equipment='DAT502'
leftjointt3
ont.day=t3.day
andt3.equipment='DAT503'
orderbyt1.day

千问 · 2021-1-27 06:42:17

http://blog.csdn.net/zeeeitch/article/details/6086257

千问 · 2021-1-27 06:42:17

http://blog.csdn.net/post_yuan/article/details/52464004