SQL行转列 列转行

--行列互转 /******************************************************************************************************************************************************  以学生成绩为例子，比较形象易懂 整理人：中国风 (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 行受影响 )*/