oracle横竖表互转

概述

一般我们数据存储在纵表里，类似于下图1。但是做报表时需要做成横表，类似于下图2。

竖（纵）表：

图1

图2

纵表转横表（做报表）

第一步：画表

SELECT name 姓名,no 考试编号,1 数学,1 语文,1 英语 from temp group by name,no order by no,name

第二步：填值

拿数学来说，通过姓名和考试编号来分组，则纵表的多行数据就变成一组。如图3。

图3

在这一组数据里可以使用max 和 sum 聚合函数都可以算出数学的成绩。 max,sum对一组数据进行运算。

max(case subject when '数学' then score else 0 end)

或者

sum(case subject when '数学' then score else 0 end)

解析：case subject when '数学' then score else 0 end

科目为数学的展示实际成绩，其他的全是0。

max（126,0,0）或者 sum（126,0,0）结果是一样的， 这样就把数学的成绩提取出来。

语句

select name 姓名,no 考试编号,
max(case subject when '数学' then score else 0 end) 数学,
max(case subject when '语文' then score else 0 end) 语文,
max(case subject when '英语' then score else 0 end) 英语 
from temp group by name,no order by no,name

横表转纵表（统计的数据入库）

第一步：画表

select 姓名 name,考试编号 no ,'subject' subject ,1 score from temp

第二步：填值

拿数学来说，'subject' 可以直接写成'数学' 。1 可以写成 数学

select 姓名 name,考试编号 no ,'数学' subject ,数学 score from temp

语句

select 姓名 name,考试编号 no ,'数学' subject ,数学 score from temp 
union all
select 姓名 name,考试编号 no ,'语文' subject ,语文 score from temp 
union all
select 姓名 name,考试编号 no ,'英语' subject ,英语 score from temp

附件

纵表

with  temp as (
 select 1 id,'张三' name,'数学' subject,126 score,1 no from dual 
 union all
 select 2 id,'张三' name,'英语' subject,130 score,1 no from dual
 union all
 select 3 id,'张三' name,'语文' subject,140 score,1 no from dual
 union all
 select 4 id,'李四' name,'数学' subject,90 score,1 no from dual 
 union all
 select 5 id,'李四' name,'英语' subject,120 score,1 no from dual
 union all
 select 6 id,'李四' name,'语文' subject,110 score,1 no from dual
 union all
 select 7 id,'张三' name,'数学' subject,130 score,2 no from dual 
 union all
 select 8 id,'张三' name,'英语' subject,140 score,2 no from dual
 union all
 select 9 id,'张三' name,'语文' subject,142 score,2 no from dual
 union all
 select 10 id,'李四' name,'数学' subject,100 score,2 no from dual 
 union all
 select 11 id,'李四' name,'英语' subject,130 score,2 no from dual
 union all
 select 12 id,'李四' name,'语文' subject,120 score,2 no from dual
)

横表

with  temp as( 
select name 姓名,no 考试编号,
sum(case subject when '数学' then score else 0 end) 数学,
sum(case subject when '语文' then score else 0 end) 语文,
sum(case subject when '英语' then score else 0 end) 英语 
from (
 select 1 id,'张三' name,'数学' subject,126 score,1 no from dual 
 union all
 select 2 id,'张三' name,'英语' subject,130 score,1 no from dual
 union all
 select 3 id,'张三' name,'语文' subject,140 score,1 no from dual
 union all
 select 4 id,'李四' name,'数学' subject,90 score,1 no from dual 
 union all
 select 5 id,'李四' name,'英语' subject,120 score,1 no from dual
 union all
 select 6 id,'李四' name,'语文' subject,110 score,1 no from dual
 union all
 select 7 id,'张三' name,'数学' subject,130 score,2 no from dual 
 union all
 select 8 id,'张三' name,'英语' subject,140 score,2 no from dual
 union all
 select 9 id,'张三' name,'语文' subject,142 score,2 no from dual
 union all
 select 10 id,'李四' name,'数学' subject,100 score,2 no from dual 
 union all
 select 11 id,'李四' name,'英语' subject,130 score,2 no from dual
 union all
 select 12 id,'李四' name,'语文' subject,120 score,2 no from dual
) group by name,no order by no,name)