最近遇到sql转置问题，今天来从sql Server和Oracle两个数据库的用法上总结一下。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想得到如下结果：
姓名 语文 数学 物理
李四 74 84 94
张三 74 83 93
建表语句就不多说了，建好测试表，插入测试数据
–SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。
–使用case when then语句
declare @sql varchar(8000)
set @sql = ‘select 姓名 ‘
select @sql = @sql + ‘ , max(case 课程 when ”’ + 课程 + ”’ then 分数 else 0 end) [' + 课程 + ']‘
from (select distinct 课程 from tb) as a
set @sql = @sql + ‘ from tb group by 姓名’
exec(@sql)
–通过动态构建@sql，得到如下脚本
select 姓名 as 姓名 ,
max(case 课程 when ‘语文’ then 分数 else 0 end) 语文,
max(case 课程 when ‘数学’ then 分数 else 0 end) 数学,
max(case 课程 when ‘物理’ then 分数 else 0 end) 物理
from tb
group by 姓名
Oracle以下面的数据为例
user_school user_name user_item user_value
——– —————- —————- —-
博客 iData_1 Linux 20
博客 iData_1 BI 2
博客 iData_1 数据库 20
博客 iData_2 Linux 10
博客 iData_2 BI 30
博客 iData_3 Linux 5
博客 iData_3 BI 2
博客 iData_3 数据库 6
博客 iData_3 数据挖掘 20
可以使用case when then 函数来做
SELECT t.user_school 类别,
t.user_name 用户,
MAX(case user_item when ‘Linux’ then user_value else 0 end) Linux,
MAX(case user_item when ‘BI’ then user_value else 0 end) BI,
MAX(case user_item when ‘数据库’ then user_value else 0 end) 数据库,
MAX(case user_item when ‘数据挖掘’ then user_value else 0 end) 数据挖掘
FROM example_row_data t
GROUP BY t.user_school
,t.user_name;
也可以使用Oracle自带的decode来做，使用decode比用case when then要方便些:
SELECT t.user_school 类别
, t.user_name 用户
,MAX(decode(user_item, ‘Linux’, user_value , 0) )Linux,
MAX(decode(user_item, ‘BI’, user_value , 0)) BI,
MAX(decode(user_item, ‘数据库’, user_value , 0)) 数据库,
MAX(decode(user_item, ‘数据挖掘’, user_value , 0) )数据挖掘
FROM example_row_data t
GROUP BY t.user_school
,t.user_name;
运行结果：
把分数显示在一行的写法：
SELECT t.user_school 类别
, t.user_name 用户
,MAX(decode(rk, 1, rpad(t.user_item || ‘:’ || t.user_value, 14, ‘ ‘), NULL)) ||
MAX(decode(rk, 2, rpad(t.user_item || ‘:’ || t.user_value, 14, ‘ ‘), NULL)) ||
MAX(decode(rk, 3, rpad(t.user_item || ‘:’ || t.user_value, 14, ‘ ‘), NULL)) ||
MAX(decode(rk, 4, rpad(t.user_item || ‘:’ || t.user_value, 14, ‘ ‘), NULL)) 科目成绩
FROM (SELECT t.*
,row_number() over(PARTITION BY t.user_name ORDER BY t.user_item) rk
FROM example_row_data t) t
GROUP BY t.user_school
,t.user_name;
运行结果为：