Oracle中行转列、列转行

1、行转列：

      原始数据：

 

1）PIVOT

      说明：PIVOT (SUM(聚合值) FOR 待转换的列名 IN (待转换的列名里面的值 转换后列的别名))；可以根据需要选择使用的聚合函数

with temp as(
  select '张三' NAME ,'语文' course,98 score from dual union all
  select '张三' NAME ,'数学' course,100 score from dual union all
  select '张三' NAME ,'物理' course,95 score from dual union all
  select '张三' NAME ,'英语' course,110 score from dual union all
  select '李四' NAME ,'语文' course,96 score from dual union all
  select '李四' NAME ,'数学' course,89 score from dual union all
  select '李四' NAME ,'物理' course,93 score from dual union all
  select '李四' NAME ,'英语' course,100 score from dual 
)
select * from (select NAME,course,score from temp) 
 pivot (max(score) for course in ('语文','数学','物理','英语'));

 

 2）CASE WHEN

说明：可以根据需要选择使用的聚合函数

with temp as(
  select '张三' NAME ,'语文' course,98 score from dual union all
  select '张三' NAME ,'数学' course,100 score from dual union all
  select '张三' NAME ,'物理' course,95 score from dual union all
  select '张三' NAME ,'英语' course,110 score from dual union all
  select '李四' NAME ,'语文' course,96 score from dual union all
  select '李四' NAME ,'数学' course,89 score from dual union all
  select '李四' NAME ,'物理' course,93 score from dual union all
  select '李四' NAME ,'英语' course,100 score from dual 
)
select NAME
      ,MAX(CASE WHEN course = '语文' THEN score END) AS 语文   
      ,MAX(CASE WHEN course = '数学' THEN score END) AS 数学   
      ,MAX(CASE WHEN course = '物理' THEN score END) AS 物理   
      ,MAX(CASE WHEN course = '英语' THEN score END) AS 英语   
  from temp
 GROUP BY NAME
;

3）DECODE

说明：可以根据需要选择使用的聚合函数

with temp as(
  select '张三' NAME ,'语文' course,98 score from dual union all
  select '张三' NAME ,'数学' course,100 score from dual union all
  select '张三' NAME ,'物理' course,95 score from dual union all
  select '张三' NAME ,'英语' course,110 score from dual union all
  select '李四' NAME ,'语文' course,96 score from dual union all
  select '李四' NAME ,'数学' course,89 score from dual union all
  select '李四' NAME ,'物理' course,93 score from dual union all
  select '李四' NAME ,'英语' course,100 score from dual 
)
select NAME
      ,max(decode(course, '语文', SCORE)) as 语文
      ,max(decode(course, '数学', SCORE)) as 数学
      ,max(decode(course, '物理', SCORE)) as 物理
      ,max(decode(course, '英语', SCORE)) as 英语
  from temp
 group by NAME
;

4）LEAD

说明：LEAD函数是Oracle数据库中的一种分析函数，用于从当前行的下一行或多行中获取数据。

语法结构：LEAD(value_expr [, offset] [, default]) OVER ([query_partition_clause] order_by_clause)

具体的用法参考：

--先使用ASCII码对中文进行排序，获取对应的顺序
with temp as(
  select '张三' NAME ,'语文' course,98 score from dual union all
  select '张三' NAME ,'数学' course,100 score from dual union all
  select '张三' NAME ,'物理' course,95 score from dual union all
  select '张三' NAME ,'英语' course,110 score from dual union all
  select '李四' NAME ,'语文' course,96 score from dual union all
  select '李四' NAME ,'数学' course,89 score from dual union all
  select '李四' NAME ,'物理' course,93 score from dual union all
  select '李四' NAME ,'英语' course,100 score from dual 
)
SELECT course, ASCII(course)
  FROM temp
 GROUP BY course
 ORDER BY course;

--按照上述已知的顺序做列转行,得到以下数据后，取：语文 IS NOT NULL
with temp as(
  select '张三' NAME ,'语文' course,98 score from dual union all
  select '张三' NAME ,'数学' course,100 score from dual union all
  select '张三' NAME ,'物理' course,95 score from dual union all
  select '张三' NAME ,'英语' course,110 score from dual union all
  select '李四' NAME ,'语文' course,96 score from dual union all
  select '李四' NAME ,'数学' course,89 score from dual union all
  select '李四' NAME ,'物理' course,93 score from dual union all
  select '李四' NAME ,'英语' course,100 score from dual 
)
SELECT *
  FROM (
    SELECT NAME
          ,LEAD(score, 0) OVER (PARTITION BY NAME ORDER BY course ) 数学
          ,LEAD(score, 1) OVER (PARTITION BY NAME ORDER BY course ) 物理
          ,LEAD(score, 2) OVER (PARTITION BY NAME ORDER BY course ) 英语
          ,LEAD(score, 3) OVER (PARTITION BY NAME ORDER BY course ) 语文
      FROM temp
  );

 

 

 

2、行转列：

说明：unpivot（自定义列名/*列的值*/ for 自定义列名/*列名*/ in（列名））

WITH TEMP AS(
SELECT '张三' NAME ,'98' 语文,'100' 数学,'95' 物理,'110' 英语 FROM DUAL 
 UNION ALL
SELECT '李四' NAME ,'96' 语文,'89' 数学,'93' 物理,'100' 英语 FROM DUAL 
)
SELECT NAME,COURSE,SCORE FROM TEMP
UNPIVOT (SCORE FOR COURSE IN (语文,数学,物理,英语))T