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;