SQL 列转行
2011-04-22 16:13:41
好东西，转一下，嘿嘿。*普通行列转换(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:Name Subject Result张三 语文 74张三 数学 83张三 物理 93李四 语文 74李四 数学 84李四 物理 94*/-------------------------------------------------------------------------/*想变成 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94张三 74 83 93*/create table tb( Name varchar(10) , Subject varchar(10) , Result int)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)go--静态SQL,指subject只有语文、数学、物理这三门课程。select name 姓名, max(case subject when '语文' then result else 0 end) 语文, max(case subject when '数学' then result else 0 end) 数学, max(case subject when '物理' then result else 0 end) 物理from tbgroup by name/*姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94张三 74 83 93*/--动态SQL,指subject不止语文、数学、物理这三门课程。declare @sql varchar(8000)set @sql = 'select Name as ' + '姓名'select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'from (select distinct Subject from tb) as aset @sql = @sql + ' from tb group by name'exec(@sql) /*姓名 数学 物理 语文 ---------- ----------- ----------- ----------- 李四 84 94 74张三 83 93 74*/-------------------------------------------------------------------/*加个平均分，总分姓名 语文 数学 物理 平均分 总分 ---------- ----------- ----------- ----------- -------------------- ----------- 李四 74 84 94 84.00 252张三 74 83 93 83.33 250*/--静态SQL,指subject只有语文、数学、物理这三门课程。select name 姓名, max(case subject when '语文' then result else 0 end) 语文, max(case subject when '数学' then result else 0 end) 数学, max(case subject when '物理' then result else 0 end) 物理, cast(avg(result*1.0) as decimal(18,2)) 平均分, sum(result) 总分from tbgroup by name/*姓名 语文 数学 物理 平均分 总分 ---------- ----------- ----------- ----------- -------------------- ----------- 李四 74 84 94 84.00 252张三 74 83 93 83.33 250*/--动态SQL,指subject不止语文、数学、物理这三门课程。declare @sql1 varchar(8000)set @sql1 = 'select Name as ' + '姓名'select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'from (select distinct Subject from tb) as aset @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'exec(@sql1) /*姓名 数学 物理 语文 平均分 总分 ---------- ----------- ----------- ----------- -------------------- ----------- 李四 84 94 74 84.00 252张三 83 93 74 83.33 250*/drop table tb ------------------------------------------------------------------------------------------------------------------/*如果上述两表互相换一下：即姓名 语文 数学 物理张三 74 83 93李四 74 84 94想变成 Name Subject Result ---------- ------- ----------- 李四 语文 74李四 数学 84李四 物理 94张三 语文 74张三 数学 83张三 物理 93*/create table tb1( 姓名 varchar(10) , 语文 int , 数学 int , 物理 int)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from( select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 union all select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1 union all select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1) torder by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------/*加个平均分,总分Name Subject Result ---------- ------- -------------------- 李四 语文 74.00李四 数学 84.00李四 物理 94.00李四 平均分 84.00李四 总分 252.00张三 语文 74.00张三 数学 83.00张三 物理 93.00张三 平均分 83.33张三 总分 250.00*/select * from( select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 union all select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1 union all select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1 union all select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1 union all select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1) torder by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1