合 PG中的行转列
Tags: PGPostgreSQL行转列
简介
PG有如下几种行转列写法:
1、group by + sum + case when
2、用postgresql的crosstab函数
3、group by + string_agg + split_part(分组,行转列,字符切割)
4、使用json函数
环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE sales ( year INTEGER, quarter INTEGER, sales_amount NUMERIC ); INSERT INTO sales VALUES (2018, 1, 100), (2018, 2, 200), (2018, 3, 300), (2018, 4, 400), (2019, 1, 500), (2019, 2, 600), (2019, 3, 700), (2019, 4, 800); |