sql server 行列轉換

標籤：io   ar   sp   on   div   bs   as   sql   nbsp   

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

sql server 行列轉換