行列互转[整理人：中国风(Roy)]

-- 行列互转
/* *****************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(Roy)

日期:2008.06.06
***************************************************************************************************************************************************** */

-- 1、行互列
-- > --> (Roy)生成測試數據

if   not   object_id ( ' Class ' )  is   null
     drop   table  Class
Go
Create   table  Class( [ Student ]   nvarchar ( 2 ), [ Course ]   nvarchar ( 2 ), [ Score ]   int )
Insert  Class
select  N ' 张三 ' ,N ' 语文 ' , 78   union   all
select  N ' 张三 ' ,N ' 数学 ' , 87   union   all
select  N ' 张三 ' ,N ' 英语 ' , 82   union   all
select  N ' 张三 ' ,N ' 物理 ' , 90   union   all
select  N ' 李四 ' ,N ' 语文 ' , 65   union   all
select  N ' 李四 ' ,N ' 数学 ' , 77   union   all
select  N ' 李四 ' ,N ' 英语 ' , 65   union   all
select  N ' 李四 ' ,N ' 物理 ' , 85  
Go
-- 2000方法:
动态:

declare   @s   nvarchar ( 4000 )
set   @s = ''
Select       @s = @s + ' , ' + quotename ( [ Course ] ) + ' =max(case when [Course]= ' + quotename ( [ Course ] , '''' ) + '  then [Score] else 0 end) '
from  Class  group   by [ Course ]
exec ( ' select [Student] ' + @s + '  from Class group by [Student] ' )


生成静态:

select  
     [ Student ] ,
     [ 数学 ] = max ( case   when   [ Course ] = ' 数学 '   then   [ Score ]   else   0   end ),
     [ 物理 ] = max ( case   when   [ Course ] = ' 物理 '   then   [ Score ]   else   0   end ),
     [ 英语 ] = max ( case   when   [ Course ] = ' 英语 '   then   [ Score ]   else   0   end ),
     [ 语文 ] = max ( case   when   [ Course ] = ' 语文 '   then   [ Score ]   else   0   end ) 
from  
    Class 
group   by   [ Student ]

GO
动态:

declare   @s   nvarchar ( 4000 )
Select       @s = isnull ( @s + ' , ' , '' ) + quotename ( [ Course ] )  from  Class  group   by [ Course ]
exec ( ' select * from Class pivot (max([Score]) for [Course] in( ' + @s + ' ))b ' )

生成静态:
select   *  
from  
    Class 
pivot 
    ( max ( [ Score ] )  for   [ Course ]   in ( [ 数学 ] , [ 物理 ] , [ 英语 ] , [ 语文 ] ))b

生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78

(2 行受影响)
*/

-- ----------------------------------------------------------------------------------------
go
-- 加上总成绩(学科平均分)

-- 2000方法:
动态:

declare   @s   nvarchar ( 4000 )
set   @s = ''
Select       @s = @s + ' , ' + quotename ( [ Course ] ) + ' =max(case when [Course]= ' + quotename ( [ Course ] , '''' ) + '  then [Score] else 0 end) '
from  Class  group   by [ Course ]
exec ( ' select [Student] ' + @s + ' ,[总成绩]=sum([Score])  from Class group by [Student] ' ) -- 加多一列(学科平均分用avg([Score]))

生成动态:

select  
     [ Student ] ,
     [ 数学 ] = max ( case   when   [ Course ] = ' 数学 '   then   [ Score ]   else   0   end ),
     [ 物理 ] = max ( case   when   [ Course ] = ' 物理 '   then   [ Score ]   else   0   end ),
     [ 英语 ] = max ( case   when   [ Course ] = ' 英语 '   then   [ Score ]   else   0   end ),
     [ 语文 ] = max ( case   when   [ Course ] = ' 语文 '   then   [ Score ]   else   0   end ),
     [ 总成绩 ] = sum ( [ Score ] )  -- 加多一列(学科平均分用avg([Score]))
from  
    Class 
group   by   [ Student ]

go

-- 2005方法:

动态:

declare   @s   nvarchar ( 4000 )
Select       @s = isnull ( @s + ' , ' , '' ) + quotename ( [ Course ] )  from  Class  group   by [ Course ]   -- isnull(@s+',','') 去掉字符串@s中第一个逗号
exec ( ' select [Student], ' + @s + ' ,[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in( ' + @s + ' ))b  ' )

生成静态:

select  
     [ Student ] , [ 数学 ] , [ 物理 ] , [ 英语 ] , [ 语文 ] , [ 总成绩 ]  
from  
    ( select   * , [ 总成绩 ] = sum ( [ Score ] ) over (partition  by   [ Student ] )  from  Class) a  -- 平均分时用avg([Score])
pivot 
    ( max ( [ Score ] )  for   [ Course ]   in ( [ 数学 ] , [ 物理 ] , [ 英语 ] , [ 语文 ] ))b 

生成格式:

/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337

(2 行受影响)
*/

go

-- 2、列转行
-- > --> (Roy)生成測試數據

if   not   object_id ( ' Class ' )  is   null
     drop   table  Class
Go
Create   table  Class( [ Student ]   nvarchar ( 2 ), [ 数学 ]   int , [ 物理 ]   int , [ 英语 ]   int , [ 语文 ]   int )
Insert  Class
select  N ' 李四 ' , 77 , 85 , 65 , 65   union   all
select  N ' 张三 ' , 87 , 90 , 82 , 78
Go

-- 2000:

动态:

declare   @s   nvarchar ( 4000 )
select   @s = isnull ( @s + '  union all  ' , '' ) + ' select [Student],[Course]= ' + quotename (Name, '''' ) -- isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+ ' ,[Score]= ' + quotename (Name) + '  from Class '
from  syscolumns  where  ID = object_id ( ' Class ' )  and  Name  not   in ( ' Student ' ) -- 排除不转换的列
order   by  Colid
exec ( ' select * from ( ' + @s + ' )t order by [Student],[Course] ' ) -- 增加一个排序

生成静态:
select   *  
from  ( select   [ Student ] , [ Course ] = ' 数学 ' , [ Score ] = [ 数学 ]   from  Class  union   all  
select   [ Student ] , [ Course ] = ' 物理 ' , [ Score ] = [ 物理 ]   from  Class  union   all  
select   [ Student ] , [ Course ] = ' 英语 ' , [ Score ] = [ 英语 ]   from  Class  union   all  
select   [ Student ] , [ Course ] = ' 语文 ' , [ Score ] = [ 语文 ]   from  Class)t 
order   by   [ Student ] , [ Course ]

go
-- 2005:

动态:

declare   @s   nvarchar ( 4000 )
select   @s = isnull ( @s + ' , ' , '' ) + quotename (Name)
from  syscolumns  where  ID = object_id ( ' Class ' )  and  Name  not   in ( ' Student ' ) 
order   by  Colid
exec ( ' select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in( ' + @s + ' ))b ' )

go
select  
    Student, [ Course ] , [ Score ]  
from  
    Class 
unpivot 
    ( [ Score ]   for   [ Course ]   in ( [ 数学 ] , [ 物理 ] , [ 英语 ] , [ 语文 ] ))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78

(8 行受影响)
*/