行列转换

数据库行列转换

 

SQLserver 2000 行列转换例子：
1.
学号，  课程号， 分数
sid     cid    mark
030611 1000 70
030611 1001 80
030611 1002 75
030611 1003 86
030611 1004 70
030612 1000 74
030612 1001 83
030612 1002 75
030612 1003 86
030612 1004 75
030613 1000 75
030613 1001 85
030613 1002 85
030613 1003 86
030613 1004 90
=>转换为下面形式：
sid     1000  1001    1002    1003     1004    总分   平均分  最高分
030611 70 80 75 86 70 381 76 86
030612 74 83 75 86 75 393 78 86
030613 75 85 85 86 90 421 84 90
方法一：
select sid ,
[1000]=sum(case cid when '1000'then mark else null end),
[1001]=sum(case cid when '1001'then mark else null end),
[1002]=sum(case cid when '1002'then mark else null end),
[1003]=sum(case cid when '1003'then mark else null end),
[1004]=sum(case cid when '1004'then mark else null end),
[总分]=sum(case sid when sid then mark else null end),
[平均分]=avg(case sid when sid then mark else null end),
[最高分]=max(case sid when sid then mark else null end)
from sc group by sid;

方法二：
  --1)声明一个字符串变量，以供动态拼装
  declare @sql varchar(8000)
  --2)拼装sql语句
  set @sql = 'select sid'
  --3)动态获得cid，为每个cid构建一个列 --把所有唯一的科目的名称都列举出来
  select @sql=@sql+',['+cid+']=sum(case cid when '''+cid+''' then mark else null end)'from (select distinct cid from sc) as sc1
  select @sql=@sql+',[总分]=sum(case sid when sid then mark else null end)'
  select @sql=@sql+',[平均分]=avg(case sid when sid then mark else null end)'
  select @sql=@sql+',[最高分]=max(case sid when sid then mark else null end)'
  select @sql=@sql+'from sc group by sid'--分组
  exec (@sql) --执行sql命令

ps:
CASE input_expression WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END
input_expression:是使用简单 CASE 格式时所计算的表达式。
when_expression:使用简单 CASE 格式时 input_expression 所比较的简单表达式。
result_expression:当 input_expression = when_expression 取值为 TRUE,即得到result_expression的值
else_result_expression:当比较运算取值不为 TRUE 时返回的表达式的值，省略else_result_expression时，
前面判断的结果不为TRUE时，默认为NULL

oracle 10g行列转换的例子：

NAME       COURSE                    GRADE
---------- -------------------- ----------
张松       语文                         74
张松       数学                         83
张松       物理                         93
李四       语文                         74
李四       数学                         84
李四       物理                         94

方法一：
 select name,
  sum(decode(course , '语文' ,grade ,0))"语文",
  sum(decode(course , '数学', grade , null))"数学",
  sum(decode(course , '物理' , grade , null))"物理",
  max(decode(name,name,grade,null))"最高分",
  sum(decode(name,name,grade,null))"总分",
  avg(decode(name,name,grade,null))"平均分"
  from tb group by name;

NAME             语文       数学       物理     最高分       总分     平均分
---------- ---------- ---------- ---------- ---------- ---------- ----------
李四               74         84         94         94        252         84
张松               74         83         93         93        250 83.3333333