SQL 行列互转

  -- 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 行受影响)
*/