服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQL 实现列转行问题

PostgreSQL 实现列转行问题

2021-03-16 20:29dev_hui PostgreSQL

这篇文章主要介绍了PostgreSQL 实现列转行问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

1 测试表数据

?
1
2
3
4
5
6
7
8
9
SELECT
  relative_label_content
FROM
  frk_s.label_cor_gene
relative_label_content
------
AA
BB
CC

2 列转行写法

写法1：

string_agg

?
1
2
3
4
5
6
7
8
9
10
SELECT
  frwybs,
  string_agg (relative_label_content, ',') as relative_label_content
FROM
  frk_s.label_cor_gene
GROUP BY
  frwybs
relative_label_content
------------
AA,BB,CC

写法2：

array_to_string(ARRAY_AGG (text),',')

?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
  frwybs,
  array_to_string(
    ARRAY_AGG (DISTINCT relative_label_content),
    ','
  ) as labels_content
FROM
  frk_s.label_cor_gene
GROUP BY
  frwybs
labels_content
------------
AA,BB,CC

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

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

pivot语法为：

?
1
2
3
4
5
6
7
SELECT ... 
FROM  ... 
PIVOT [XML] 
  (pivot_clause 
  pivot_for_clause 
  pivot_in_clause ) 
WHERE ...

oracle pivot使用例子：

–创建测试表并插入数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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进行行列转换

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

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

unpivot语法为：

?
1
2
3
4
5
6
7
SELECT ... 
FROM ... 
UNPIVOT [INCLUDE|EXCLUDE NULLS] 
  (unpivot_clause 
  unpivot_for_clause 
  unpivot_in_clause ) 
WHERE ...

oracle unpivot使用例子：

–创建测试表并插入数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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进行列转行

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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     5
25 rows selected.

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

pg行转列例子：

–建表插入测试数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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; 

–行转列

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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)

–列转行

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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)

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

原文链接：https://blog.csdn.net/HSH205572/article/details/83743990

  • 列转行
  • PostgreSQL

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图