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

 

 

转载于:https://www.cnblogs.com/wanglinglong/archive/2009/12/16/1625268.html