行列互换(一)

原文转自：http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

--行列互转

/******************************************************************************************************************************************************

以学生成绩为例子，比较形象易懂

整理人：中国风(Roy)

日期:2008.06.06

******************************************************************************************************************************************************

/--1、行互列

-> --> (Roy)生成測試數據

if notobject_id('Class')isnull

      droptable Class

Go

Createtable Class([Student]nvarchar(2),[Course]nvarchar(2),[Score]int)

Insert Class

       select N'张三',N'语文',78unionallselect N'张三',N'数学',87unionall

       select N'张三',N'英语',82unionallselect N'张三',N'物理',90unionall

       select N'李四',N'语文',65unionallselect N'李四',N'数学',77unionall

       select N'李四',N'英语',65unionallselect 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(casewhen[Course]='数学'then[Score]else0end),

     [物理]=max(casewhen[Course]='物理'then[Score]else0end),

     [英语]=max(casewhen[Course]='英语'then[Score]else0end),

     [语文]=max(casewhen[Course]='语文'then[Score]else0end)

from Class

group by[Student]

GO

动态:

declare @s nvarchar(4000)

Select @s=isnull(@s+',','')+quotename([Course])from Class groupby[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(casewhen[Course]='数学'then[Score]else0end),

       [物理]=max(casewhen[Course]='物理'then[Score]else0end),

       [英语]=max(casewhen[Course]='英语'then[Score]else0end),

       [语文]=max(casewhen[Course]='语文'then[Score]else0end),

       [总成绩]=sum([Score])--加多一列(学科平均分用avg([Score]))

from Class

group by[Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)

Select @s=isnull(@s+',','')+quotename([Course])from Class groupby[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(partitionby[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 notobject_id('Class')isnull

drop table Class

Go

Createtable Class([Student]nvarchar(2),[数学]int,[物理]int,[英语]int,[语文]int)

Insert Class

select N'李四',77,85,65,65unionall

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')--排除不转换的列

orderby Colid

exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:

select *

from

      (select[Student],[Course]='数学',[Score]=[数学]from Class unionall

      select [Student],[Course]='物理',[Score]=[物理]from Class unionall

      select [Student],[Course]='英语',[Score]=[英语]from Class unionall

      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')

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

*/