PostgreSQL 实现列转行问题

1 测试表数据

SELECT  relative_label_contentFrom  frk_s.label_cor_generelative_label_content------AABBCC

2 列转行写法

写法1：

string_agg

SELECT  frwybs,  string_agg (relative_label_content, ',') as relative_label_contentFrom  frk_s.label_cor_genegroup by  frwybsrelative_label_content------------AA,BB,CC

写法2：

array_to_string(ARRAY_AGG (text),',')

SELECT  frwybs,  array_to_string(    ARRAY_AGG (DISTINCT relative_label_content),    ','  ) as labels_contentFROM  frk_s.label_cor_geneGROUP BY  frwybslabels_content------------AA,BB,CC

补充：PostgreSQL行列转换(兼容oracle pivot unpivot)

oracle11g开始内置了数据透视表pivot table这一功能，可以用来实现行列转换的功能，但是在数据量较大的时候使用性能就会较差。

pivot语法为：

SELECT ...  FROM  ...  PIVOT [XML]    (pivot_clause    pivot_for_clause    pivot_in_clause )  WHERE ... 

oracle pivot使用例子：

–创建测试表并插入数据

create table usr(name vArchar2(20), score int, class VARchar2(20));
    insert into usr values('a',20,'math');
    insert into usr values('a',22,'phy');
    insert into usr values('b',23,'phy');
    insert into usr values('b',21,'math');
    insert into usr values('c',22,'phy');
    insert into usr values('c',24,'math');
    insert into usr values('d',25,'math');
    insert into usr values('d',23,'phy');
    

–使用pivot进行行列转换

SQL>
     select * from usr 2 pivot(sum(score)for class in ('math','phy') 3  4  5 );
    NAME           'math'   'phy'-------------------- ---------- ----------d              25     23a              20     22b              21     23c              24     22

我们还可以使用unpivot来实现列转行。

unpivot语法为：

SELECT ...  FROM ...  UNPIVOT [INCLUDE|EXCLUDE NULLS]    (unpivot_clause    unpivot_for_clause    unpivot_in_clause )  WHERE ... 

oracle unpivot使用例子：

–创建测试表并插入数据

CREATE TABLE t1 (  VendorID int,   Emp1 int,   Emp2 int,   EmP3 int,   Emp4 int,  Emp5 int);
     INSERT INTO t1 VALUES (1,4,3,5,4,4);
     INSERT INTO t1 VALUES (2,4,1,5,5,5);
     INSERT INTO t1 VALUES (3,4,3,5,4,4);
     INSERT INTO t1 VALUES (4,4,2,5,5,4);
     INSERT INTO t1 VALUES (5,5,1,5,5,5);
     

–使用unpivot进行列转行

SQL>
     select * from t1 2 UNPIVOT(orders for Employee in(emp1,emp2,emp3,emp4,emp5));
     3  4  VENDORID EMPL   ORDERS---------- ---- ----------     1 EMP1     4     1 EMP2     3     1 EMP3     5     1 EMP4     4     1 EMP5     4     2 EMP1     4     2 EMP2     1     2 EMP3     5     2 EMP4     5     2 EMP5     5     3 EMP1     4 VENDORID EMPL   ORDERS---------- ---- ----------     3 EMP2     3     3 EMP3     5     3 EMP4     4     3 EMP5     4     4 EMP1     4     4 EMP2     2     4 EMP3     5     4 EMP4     5     4 EMP5     4     5 EMP1     5     5 EMP2     1 VENDORID EMPL   ORDERS---------- ---- ----------     5 EMP3     5     5 EMP4     5     5 EMP5     525 rows selected.

那么在Pg中该如何实现oracle的pivot/unpivot的行列转行功能呢？pg中自带的tablefunc插件可以实现，我们可以使用该插件中的crosstab函数接口进行行列转换。

pg行转列例子：

–建表插入测试数据

create table tbl (seller text,se_year int,se_month int,se_amount int);
     insert into tbl values ('test1',2020,01,123456);
       insert into tbl values ('test1',2020,02,234567);
       insert into tbl values ('test1',2020,03,345678);
       insert into tbl values ('test1',2020,04,345678);
       insert into tbl values ('test1',2020,05,567890);
       insert into tbl values ('test2',2020,01,12);
       insert into tbl values ('test2',2020,02,23);
       insert into tbl values ('test2',2020,03,34);
       insert into tbl values ('test2',2020,04,45);
       insert into tbl values ('test2',2020,05,56);
       insert into tbl values ('test3',2020,03,12);
       insert into tbl values ('test3',2020,04,45);
       insert into tbl values ('test3',2020,05,56);
       insert into tbl values ('test4',2020,02,20);
       insert into tbl values ('test4',2020,03,30);
       insert into tbl values ('test4',2020,04,40);
       insert into tbl values ('test4',2020,05,50);
       insert into tbl values ('test1',2019,01,123456);
       insert into tbl values ('test1',2019,02,234567);
       insert into tbl values ('test1',2019,03,345678);
       insert into tbl values ('test1',2019,04,345678);
       insert into tbl values ('test1',2019,05,567890);
       insert into tbl values ('test1',2019,06,123456);
       insert into tbl values ('test1',2019,07,234567);
       insert into tbl values ('test1',2019,08,345678);
       insert into tbl values ('test1',2019,09,345678);
       insert into tbl values ('test1',2019,10,567890);
       insert into tbl values ('test1',2019,11,123456);
       insert into tbl values ('test1',2019,12,234567);
       insert into tbl values ('test2',2019,11,12);
       insert into tbl values ('test2',2019,12,23);
    insert into tbl select * from tbl;
      

–行转列

bill=# select   bill-#  js->
    >
    'seller' as seller,    bill-#  js->
    >
    'se_year' as se_year,   bill-#  jan ,  bill-#  feb ,  bill-#  mar ,  bill-#  aPR ,  bill-#  may ,  bill-#  jun ,  bill-#  jul ,  bill-#  aug ,  bill-#  sep ,  bill-#  oct ,  bill-#  nov ,  bill-#  dec   bill-# from crosstab(  bill(#  -- 这个是需要进行行列变换的源SQL ， 数据源。  bill(#  -- 排序字段为group by字段 ，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）  bill(#  -- （必须在下一个参数中提取出对应的所有枚举值）  bill(#  $$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl group by 1,2 order by 1$$,    bill(#  -- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值   bill(#  -- 或(select * from (values('jan'),...('dec')) t(se_month))  bill(#  'select distinct se_month from tbl order by 1'     bill(# )   bill-# as  -- crosstab 输出格式  bill-# ( js jsonb, -- 第一个参数SQL内对应的order by对应的字段（1个或多个）  bill(#  Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列）  bill(#  feb numeric, -- ...同上  bill(#  mar numeric,  bill(#  apr numeric,  bill(#  may numeric,  bill(#  jun numeric,  bill(#  jul numeric,  bill(#  aug numeric,  bill(#  sep numeric,  bill(#  oct numeric,  bill(#  nov numeric,  bill(#  dec numeric  bill(# )   bill-# order by 1,2;
       seller | se_year | jan  | feb  | mar  | apr  |  may  | jun  | jul  | aug  | sep  |  oct  | nov  | dec  --------+---------+--------+--------+--------+--------+---------+--------+--------+--------+--------+---------+--------+-------- test1 | 2019  | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134 test1 | 2020  | 246912 | 469134 | 691356 | 691356 | 1135780 |    |    |    |    |     |    |     test2 | 2019  |    |    |    |    |     |    |    |    |    |     |   24 |   46 test2 | 2020  |   24 |   46 |   68 |   90 |   112 |    |    |    |    |     |    |     test3 | 2020  |    |    |   24 |   90 |   112 |    |    |    |    |     |    |     test4 | 2020  |    |   40 |   60 |   80 |   100 |    |    |    |    |     |    |    (6 rows)

–列转行

bill=# wITh a as ( -- A对应原始数据（即需要列转行的数据） bill(# select   bill(#  js->
    >
    'seller' as seller,    bill(#  js->
    >
    'se_year' as se_year,   bill(#  jan ,  bill(#  feb ,  bill(#  mar ,  bill(#  apr ,  bill(#  may ,  bill(#  jun ,  bill(#  jul ,  bill(#  aug ,  bill(#  sep ,  bill(#  oct ,  bill(#  nov ,  bill(#  dec   bill(# from crosstab(  bill(#  -- 这个是需要进行行列变换的源SQL ， 数据源。  bill(#  -- 排序字段为group by字段 ，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）  bill(#  -- （必须在下一个参数中提取出对应的所有枚举值）  bill(#  $$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl group by 1,2 order by 1$$,    bill(#  -- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值   bill(#  -- 或(select * from (values('jan'),...('dec')) t(se_month))  bill(#  'select distinct se_month from tbl order by 1'     bill(# )   bill(# as  -- crosstab 输出格式  bill(# ( js jsonb, -- 第一个参数SQL内对应的order by对应的字段（1个或多个）  bill(#  Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列）  bill(#  feb numeric, -- ...同上  bill(#  mar numeric,  bill(#  apr numeric,  bill(#  may numeric,  bill(#  jun numeric,  bill(#  jul numeric,  bill(#  aug numeric,  bill(#  sep numeric,  bill(#  oct numeric,  bill(#  nov numeric,  bill(#  dec numeric  bill(# )   bill(# order by 1,2  bill(# )  bill-# ,   bill-# -- b ， 用jsonb把多列合并为一列，并使用jsonb_each展开。 bill-# b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec from a)   bill-# select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;
       seller | se_year | month |  sum  --------+---------+-------+--------- test1 | 2019  | apr  | 691356 test1 | 2019  | aug  | 691356 test1 | 2019  | dec  | 469134 test1 | 2019  | feb  | 469134 test1 | 2019  | jan  | 246912 test1 | 2019  | jul  | 469134 test1 | 2019  | jun  | 246912 test1 | 2019  | mar  | 691356 test1 | 2019  | may  | 1135780 test1 | 2019  | nov  | 246912 test1 | 2019  | oct  | 1135780 test1 | 2019  | sep  | 691356 test1 | 2020  | apr  | 691356 test1 | 2020  | aug  | null test1 | 2020  | dec  | null test1 | 2020  | feb  | 469134 test1 | 2020  | jan  | 246912 test1 | 2020  | jul  | null test1 | 2020  | jun  | null test1 | 2020  | mar  | 691356 test1 | 2020  | may  | 1135780 test1 | 2020  | nov  | null test1 | 2020  | oct  | null test1 | 2020  | sep  | null test2 | 2019  | apr  | null test2 | 2019  | aug  | null test2 | 2019  | dec  | 46 test2 | 2019  | feb  | null test2 | 2019  | jan  | null test2 | 2019  | jul  | null test2 | 2019  | jun  | null test2 | 2019  | mar  | null test2 | 2019  | may  | null test2 | 2019  | nov  | 24 test2 | 2019  | oct  | null test2 | 2019  | sep  | null test2 | 2020  | apr  | 90 test2 | 2020  | aug  | null test2 | 2020  | dec  | null test2 | 2020  | feb  | 46 test2 | 2020  | jan  | 24 test2 | 2020  | jul  | null test2 | 2020  | jun  | null test2 | 2020  | mar  | 68 test2 | 2020  | may  | 112 test2 | 2020  | nov  | null test2 | 2020  | oct  | null test2 | 2020  | sep  | null test3 | 2020  | apr  | 90 test3 | 2020  | aug  | null test3 | 2020  | dec  | null test3 | 2020  | feb  | null test3 | 2020  | jan  | null test3 | 2020  | jul  | null test3 | 2020  | jun  | null test3 | 2020  | mar  | 24 test3 | 2020  | may  | 112 test3 | 2020  | nov  | null test3 | 2020  | oct  | null test3 | 2020  | sep  | null test4 | 2020  | apr  | 80 test4 | 2020  | aug  | null test4 | 2020  | dec  | null test4 | 2020  | feb  | 40 test4 | 2020  | jan  | null test4 | 2020  | jul  | null test4 | 2020  | jun  | null test4 | 2020  | mar  | 60 test4 | 2020  | may  | 100 test4 | 2020  | nov  | null test4 | 2020  | oct  | null test4 | 2020  | sep  | null(72 rows)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• PostgreSQL 实现将多行合并转为列
• PostgreSQL实现交叉表（行列转换）的5种方法示例
• postgresql高级应用之行转列& 汇总求和的实现思路

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！