数据库行列交换实例

 请写SQL语句将下表test
person   ttime   Iotype
001   8:00   1
001   12:00   2
001   13:00   1
001   17:00   2
002   8:00   1
002   12:00   2

转换成下表
wpid   person   in   out
1   001   8:00   12:00
2   001   13:00   17:00
3   001   8:00   12:00  

/**/ /*
普通行列转换
(爱新觉罗.毓华 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  tb
group   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  a
set   @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  tb
group   by  name
/**/ /*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250