952
社区成员
发帖
与我相关
我的任务
分享postgre数据库如何实现行转列
create table t(day date,equipment varchar(20),output integer);
insert into t values('2010-04-01','DAT501',100);
insert into t values('2010-04-01','DAT502',120);
insert into t values('2010-04-01','DAT503',130);
insert into t values('2010-04-02','DAT501',110);
insert into t values('2010-04-02','DAT502',105);
insert into t values('2010-04-03','DAT503',125);
insert into t values('2010-04-04','DAT501',100);
insert into t values('2010-04-04','DAT503',200);
--想得到如下结果
day | dat501 | dat502 | dat503
----------------+-----------+----------+--------
2010-04-01 | 100 | 120 | 130
2010-04-02 | 110 | 105 |
2010-04-03 | | | 125
2010-04-04 | 100 | | 200
insert into t values('2010-04-01','DAT501',100);
insert into t values('2010-04-01','DAT502',120);
insert into t values('2010-04-01','DAT503',130);
insert into t values('2010-04-02','DAT501',110);
insert into t values('2010-04-02','DAT502',105);
insert into t values('2010-04-03','DAT503',125);
insert into t values('2010-04-04','DAT501',100);
insert into t values('2010-04-04','DAT503',200);
--想得到如下结果
day | dat501 | dat502 | dat503
----------------+-----------+----------+--------
2010-04-01 | 100 | 120 | 130
2010-04-02 | 110 | 105 |
2010-04-03 | | | 125
2010-04-04 | 100 | | 200
...全文
请发表友善的回复…
发表回复
#0xZ 2018-01-06
- 打赏
- 举报
http://blog.csdn.net/post_yuan/article/details/52464004
zeeeitch 2017-09-11
- 打赏
- 举报
http://blog.csdn.net/zeeeitch/article/details/6086257
何小牛 2017-09-01
- 打赏
- 举报
这个应该只能通过关联的方式解决：
select t.day , t1.equipment DAT501 ,t2.equipment DAT502 ,t3.equipment DAT503
from (select distinct day from t ) t
left join t t1
on t.day =t1.day
and t1. equipment = 'DAT501'
left join t t2
on t.day =t2.day
and t2. equipment = 'DAT502'
left join t t3
on t.day =t3.day
and t3. equipment = 'DAT503'
order by t1.day
