MS SQL中的行轉列

create table tb
(
    LocTion nvarchar(20),
    work nvarchar(40),
    money int,
    time int,
    age int
)

insert tb select '广州',                   '程序员'      , 1000 ,          5          ,   22 
insert tb select '广州' ,                  '教师'       ,    1700,           10      ,     22 
insert tb select '广州'  ,                 '警察'      ,     1300 ,          15     ,        22 
insert tb select '广州'   ,                '警察'     ,      800   ,          5    ,       22 
insert tb select '上海'    ,               '程序员'  ,     1600     ,      5      ,       21 
insert tb select '上海'     ,              '司机'   ,        1200    ,       15  ,         21 
insert tb select '北京'      ,             '程序员',       1400       ,    5    ,         29

declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+',sum(case when time='+cast(time as varchar)+' then money else 0 end) as ['+cast(time as varchar)+']'
from (select distinct time from tb) a

exec ('select LocTion,Work'+@sql+',max(age) as age from tb group by LocTion,Work order by LocTion,Work')

drop table tb

/*
LocTion              Work            5           10          15          age         
-------------------- ---------------- ----------- ----------- ----------- ----------- 
北京                   程序员              1400        0           0           29
广州                   程序员              1000        0           0           22
广州                   教师                0           1700        0           22
广州                   警察                800         0           1300        22
上海                   程序员              1600        0           0           21
上海                   司机                0           0           1200        21

*/

  1 /*
  2 普通行列转换
  3 (爱新觉罗.毓华 2007-11-18于海南三亚)
  4 
  5 假设有张学生成绩表(tb)如下:
  6 Name Subject Result
  7 张三 语文　　74
  8 张三 数学　　83
  9 张三 物理　　93
 10 李四 语文　　74
 11 李四 数学　　84
 12 李四 物理　　94
 13 */
 14 
 15 -------------------------------------------------------------------------
 16 /*
 17 想变成 
 18 姓名         语文        数学        物理          
 19 ---------- ----------- ----------- ----------- 
 20 李四         74          84          94
 21 张三         74          83          93
 22 */
 23 
 24 create table tb
 25 (
 26    Name    varchar(10) ,
 27    Subject varchar(10) ,
 28    Result  int
 29 )
 30 
 31 insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
 32 insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
 33 insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
 34 insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
 35 insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
 36 insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
 37 go
 38 
 39 --静态SQL,指subject只有语文、数学、物理这三门课程。
 40 select name 姓名,
 41   max(case subject when '语文' then result else 0 end) 语文,
 42   max(case subject when '数学' then result else 0 end) 数学,
 43   max(case subject when '物理' then result else 0 end) 物理
 44 from tb
 45 group by name
 46 /*
 47 姓名         语文        数学        物理          
 48 ---------- ----------- ----------- ----------- 
 49 李四         74          84          94
 50 张三         74          83          93
 51 */
 52 
 53 --动态SQL,指subject不止语文、数学、物理这三门课程。
 54 declare @sql varchar(8000)
 55 set @sql = 'select Name as ' + '姓名'
 56 select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
 57 from (select distinct Subject from tb) as a
 58 set @sql = @sql + ' from tb group by name'
 59 exec(@sql) 
 60 /*
 61 姓名         数学        物理        语文          
 62 ---------- ----------- ----------- ----------- 
 63 李四         84          94          74
 64 张三         83          93          74
 65 */
 66 
 67 -------------------------------------------------------------------
 68 /*加个平均分，总分
 69 姓名         语文        数学        物理        平均分                总分          
 70 ---------- ----------- ----------- ----------- -------------------- ----------- 
 71 李四         74          84          94          84.00                252
 72 张三         74          83          93          83.33                250
 73 */
 74 
 75 --静态SQL,指subject只有语文、数学、物理这三门课程。
 76 select name 姓名,
 77   max(case subject when '语文' then result else 0 end) 语文,
 78   max(case subject when '数学' then result else 0 end) 数学,
 79   max(case subject when '物理' then result else 0 end) 物理,
 80   cast(avg(result*1.0) as decimal(18,2)) 平均分,
 81   sum(result) 总分
 82 from tb
 83 group by name
 84 /*
 85 姓名         语文        数学        物理        平均分                总分          
 86 ---------- ----------- ----------- ----------- -------------------- ----------- 
 87 李四         74          84          94          84.00                252
 88 张三         74          83          93          83.33                250
 89 */
 90 
 91 --动态SQL,指subject不止语文、数学、物理这三门课程。
 92 declare @sql1 varchar(8000)
 93 set @sql1 = 'select Name as ' + '姓名'
 94 select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
 95 from (select distinct Subject from tb) as a
 96 set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
 97 exec(@sql1) 
 98 /*
 99 姓名         数学        物理        语文        平均分                总分          
 ---------- ----------- ----------- ----------- -------------------- ----------- 
 李四         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
 ) t
 order 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
 ) t
 order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
 
 drop table tb1

create table t
(
    LocTion nvarchar(20),
    work nvarchar(40),
    money int,
    time int,
    age int
)

insert t select '广州',                   '程序员'      , 1000 ,          5          ,   22 
insert t select '广州' ,                  '教师'       ,    1700,           10      ,     22 
insert t select '广州'  ,                 '警察'      ,     1300 ,          15     ,        22 
insert t select '广州'   ,                '警察'     ,      800   ,          5    ,       22 
insert t select '上海'    ,               '程序员'  ,     1600     ,      5      ,       21 
insert t select '上海'     ,              '司机'   ,        1200    ,       15  ,         21 
insert t select '北京'      ,             '程序员',       1400       ,    5    ,         29

go


declare @s nvarchar(4000)
set @s='select LocTion,work'
select @s=@s+','+quotename(time)+'=sum(case when time='+rtrim(time) +' then money else 0 end)'
from t group by time

exec (@s+',age from t group by LocTion,work,age')
--生成的语句:
select LocTion,work,[5]=sum(case when time=5 then money else 0 end),[10]=sum(case when time=10 then money else 0 end),[15]=sum(case when time=15 then money else 0 end),age from t group by LocTion,work,age
LocTion              work                                     5           10          15          age         
-------------------- ---------------------------------------- ----------- ----------- ----------- ----------- 
北京                   程序员                                      1400        0           0           29
广州                   程序员                                      1000        0           0           22
广州                   教师                                       0           1700        0           22
广州                   警察                                       800         0           1300        22
上海                   程序员                                      1600        0           0           21
上海                   司机                                       0           0           1200        21

create table t
(
    LocTion nvarchar(20),
    work nvarchar(40),
    money int,
    time int,
    age int
)

insert t select '广州',                   '程序员'      , 1000 ,          5          ,   22 
insert t select '广州' ,                  '教师'       ,    1700,           10      ,     22 
insert t select '广州'  ,                 '警察'      ,     1300 ,          15     ,        22 
insert t select '广州'   ,                '警察'     ,      800   ,          5    ,       22 
insert t select '上海'    ,               '程序员'  ,     1600     ,      5      ,       21 
insert t select '上海'     ,              '司机'   ,        1200    ,       15  ,         21 
insert t select '北京'      ,             '程序员',       1400       ,    5    ,         29

go

select 
    LocTion,
    work,
    [5]=sum(case when time=5 then money else 0 end),
    [10]=sum(case when time=10 then money else 0 end),
    [15]=sum(case when time=15 then money else 0 end),
    age ,
    row=identity(int,1,1) 
    into ##--生成临时表
from 
    t 
group by LocTion,work,age  order by age%2 asc, age asc


select 
    [LocTion]=case when row=(select min(row) from ## where [LocTion]=a.[LocTion]) then [LocTion] else ''end,
    [work],[5],[10],[15],age
from 
    ## a

--drop table ##



LocTion              work                                     5           10          15          age         
-------------------- ---------------------------------------- ----------- ----------- ----------- ----------- 
广州                   程序员                                      1000        0           0           22
                     教师                                       0           1700        0           22
                     警察                                       800         0           1300        22
上海                   程序员                                      1600        0           0           21
                     司机                                       0           0           1200        21
北京                   程序员                                      1400        0           0           29

（所影响的行数为 6 行）