PostgreSQL 行转列

1. crosstab 函数

参见

推荐，更灵活。

2. CASE WHEN

--1、建表
db_test=# create table db_test.t_sales_amount (n_id int4,c_name varchar(100),n_quarter int2,n_amount numeric(20,2));
--2、插入数据
db_test=# insert into db_test.t_sales_amount(n_id, c_name, n_quarter, n_amount) values
(1,'张三',1,100),(1,'张三',2,200),(1,'张三',3,300),(1,'张三',4,400),
(2,'李四',1,500),(2,'李四',2,600),(2,'李四',3,700),(2,'李四',4,800);
--SQL
select c_name
     ,sum(case when n_quarter=1 then n_amount end) as 一季度
     ,sum(case when n_quarter=2 then n_amount end) as 二季度
     ,sum(case when n_quarter=3 then n_amount end) as 三季度
     ,sum(case when n_quarter=4 then n_amount end) as 四季度
from db_test.t_sales_amount
group by c_name

3. string_agg+split_part

SELECT c_name,
       --切割
    split_part(amount_agg, ',', 1)::numeric AS 一季度,
    split_part(amount_agg, ',', 2)::numeric AS 二季度,
    split_part(amount_agg, ',', 3)::numeric AS 三季度,
    split_part(amount_agg, ',', 4)::numeric AS 四季度
FROM (
    SELECT c_name
         --聚合
         , string_agg(n_amount::text, ',' ORDER BY n_quarter) AS amount_agg
    FROM db_test.t_sales_amount
    GROUP BY c_name
) aa;

参考