2.Oracle、pgsql列转行、行转列

Oracle、pgsql列转行、行转列

作者：手心儿有糖
日期：2022年7月4日

一、Oracle列转行

关键字：unpivot

demno1:

（1）先获取数据

with 排名表 as
(select a.*
,dense_rank() over(order by chinese desc) chinese_rank
,dense_rank() over(order by math desc) math_rank
  from (select 'A' as name ,36 chinese, 67 math FROM dual
        union all                                
        select 'B' as name ,56 chinese, 47 math FROM dual
        union all                               
        select 'C' as name ,78 chinese, 58 math FROM dual
        union all                                
        select 'D' as name ,53 chinese, 96 math FROM dual
        union all                                
        select 'E' as name ,87 chinese, 63 math FROM dual
        ) a
 order by name
)
SELECT * FROM 排名表

（2）列转行

需求分析：

1.添加学科列，字段名为“学科”，取分别为“语文”，“数学”

2.将语文成绩和数学成绩两列转成一列，并给新字段名为“成绩”

3.将语文排名和数学排名两列转成一列，并给新字段名为“排名”

select name,学科,成绩,排名
from 排名表
unpivot-- 列转行
( (成绩,排名) for 学科 in ((chinese,chinese_rank) as '语文',
                         (math   ,math_rank  ) as '数学')
)
order by name

unpivot用法总结：select 原字段1，新字段名1，新字段名2，新字段名3 别名from 表名 unpivot （

（新字段名2，新字段名3） for

​ 新字段名1 in （（原字段2，原字段3） as 新字段名1值1,

​ (原字段4，原字段5) as 新字段名1值2，

​ …）

）

二、Oracle行转列

关键字：pivot

demno1:

(1)获取数据

with 排名表 as
(select 'A' as name ,'语文' sub, 67 sco FROM dual
        union all                                
        select 'A' as name ,'数学' sub, 47 sco FROM dual
        union all                               
        select 'B' as name ,'语文' sub, 58 sco FROM dual
        union all                                
        select 'B' as name ,'数学' sub, 96 sco FROM dual  
        ) 

SELECT * FROM 排名表

（2）pivot行转列

需求分析：

1.NAME字段一列转变多列

2.将科目一列转成两列，分别为“语文”、“数学”。值为对应的分数。

SELECT *
FROM 排名表 
pivot (
	sum(sco) FOR sub IN ('语文' 语文,'数学' 数学) 
) 
ORDER BY name;

pivot用法总结：select * from 表名 pivot （

sum(做为值的字段名) for

​ 拆分的原字段名 in （值1 新列名1，值2 新列名2 ，…）

）ORDER BY 未变动的列名

三、pgsql列转行

关键字：split_part、unnest 、string_to_array

（1）获取数据

with 排名表 as
(select 'A' as name , 67 语文,47 数学
        union all                               
        select 'B' as name ,58 语文,96 数学
         
        )  
select * from 排名表

（2）列转行

1、先拼接转换成字符串的值，然后使用string_to_array，将指定符号分割开的内容转数组。
2、然后将转换后的数组使用unnest进行行转置。
3、最后根据split_part进行字符串切割，使用索引取指定列的值。

with tmp as (
select name,'语文' || '~' || 语文 || ',' || '数学' || '~' || 数学 as concat_co from 排名表)
select * from tmp

select name,split_part(unnest(string_to_array(concat_co,',')),'~',1) as 科目,
split_part(unnest(string_to_array(concat_co,',')),'~',2) as 成绩 from tmp

四、Pgsql行专列

关键字：sum … filter(where…)

（1）获取数据

with 排名表 as
(select 'A' as name ,'语文' 科目, 67 成绩 
        union all                                
        select 'A' as name ,'数学' 科目, 47 成绩 
        union all                               
        select 'B' as name ,'语文' 科目, 58 成绩 
        union all                                
        select 'B' as name ,'数学' 科目, 96 成绩
)
select * from 排名表

（2）使用filter行转列

 select name,
            sum(成绩) filter(where 科目='语文') 语文,sum(成绩) filter(where 科目='数学') 数学
    from 排名表
    group by name