SQL Server 行列互转实现小结

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

 
--行列互转 
/****************************************************************************************************************************************************** 
以学生成绩为例子，比较形象易懂 

整理人：中国风(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'李四',65 union all 
select N'李四',77 union all 
select 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+','''')+' 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),[总成绩]=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,[Score] 
from 
Class 
unpivot 
([Score] for [Course] in([数学],[语文]))b 

生成格式: 
/* 
Student Course Score 
------- ------- ----------- 
李四 数学 77 
李四 物理 85 
李四 英语 65 
李四 语文 65 
张三 数学 87 
张三 物理 90 
张三 英语 82 
张三 语文 78 

(8 行受影响) 
*/ 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!