sqlserver 行列互轉完成小結。本站提示廣大學習愛好者：（sqlserver 行列互轉完成小結）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver 行列互轉完成小結正文
--行列互轉
/******************************************************************************************************************************************************
以先生成就為例子，比擬抽象易懂
整頓人：中國風(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 行受影響)
*/
