梧桐数据库的行转列

最新推荐文章于 2025-12-31 19:19:25 发布

原创

最新推荐文章于 2025-12-31 19:19:25 发布 · 4.5k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #服务器 #运维

简介

梧桐数据库的两种行转列写法：

group by + sum + case when
group by + string_agg + split_part(分组，行转列，字符切割)

环境准备

--建表
CREATE TABLE public.sales (
	"year" int4 NOT NULL,
	quarter int4 NOT NULL,
	sales_amount numeric NULL
);

--插入数据
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2018, 1, 100);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2018, 2, 200);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2018, 3, 300);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2018, 4, 400);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2019, 1, 500);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2019, 2, 600);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2019, 3, 700);
INSERT INTO public.sales ("year", quarter, sales_amount) VALUES(2019, 4, 800);

我们想将每个季度的销售额作为一列，年份作为行，结果：