列转行上一篇博客已经介绍过了。
下面介绍一下行转列的实现
假设我们有一个数据表：
CREATE TABLE row_to_line
(
user_name character varying(30) NOT NULL, -- 学生名称
yingyu integer, -- 得分
yuwen integer,
huaxue integer,
wuli integer,
CONSTRAINT row_to_line_pkey PRIMARY KEY (user_name)
); insert into row_to_line select 'liqiu', 80, 90, 90, 89;
insert into row_to_line select 'lingling', 89, 99, 100, 90;
insert into row_to_line select 'xingxing', 90, 94, 97, 99;
显示如下：
那么我们想要将它转化为一列列的如下结果输出：
那么如何做到哪？
方法一、简单可读性强：
select
a.user_name,
a.title,
a.score
from
(
(select user_name, yingyu as "score", 'yingyu' as title from row_to_line)
union (select user_name, yuwen as "score", 'yuwen' as title from row_to_line)
union (select user_name, huaxue as "score", 'huaxue' as title from row_to_line)
union (select user_name, wuli as "score", 'wuli' as title from row_to_line)
) a
order by a.user_name, a.title
方法二、快速
这是pg的专有方法
select * from tmp.dim_values_20170821 limit 10
select lower(regexp_split_to_table(dim_values, ' ')) as dim_name, table_name,all_num from tmp.dim_values_20170821 where table_name = '景区统计表'