postgresql行转列与列转行图文教程

目录

• 列转行
• 行转列
• 总结

select t.bid_unit,unit_id from unit t
where t.unit_id=1947;
result=> 中国信息通信研究院;北京市海淀区学院
-- by zhengkai.blog.csdn.net

select unnest(string_to_array(t.bid_unit,';')),unit_id from unit t
where t.unit_id=1947;
result=> 
中国信息通信研究院
北京市海淀区学院
-- by zhengkai.blog.csdn.net

-- by zhengkai.blog.csdn.net
create table sales(year int, month int, qty int);
insert into sales values(2022, 1, 1000);
insert into sales values(2022, 2, 1500);
insert into sales values(2022, 7, 500);
insert into sales values(2022, 11, 1500);
insert into sales values(2022, 12, 2000);
insert into sales values(2023, 1, 1200);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2022 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2023 | 1200 |      |     |     |     |     |     |     |     |     |      |
(2 rows)