sqlserver 行列互转
发布时间：2020-12-12 14:00:35 所属栏目：MsSql教程 来源：网络整理
导读：--行列互转 /****************************************************************************************************************************************************** 以学生成绩为例子，比较形象易懂 ? 整理人：中国风(Roy) ? 日期:2008.06.06 *****
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂
?
整理人：中国风(Roy)
?
日期:2008.06.06
******************************************************************************************************************************************************/
?
--1、行互列
-->?-->?(Roy)生成測試數據
??
if?
not?
object_id(
'Class'
)?
is?
null
????
drop?
table?
Class
Go
Create?
Class([Student]?nvarchar(2),[Course]?nvarchar(2),[Score]?
int
)
Insert?
Class
select?
N
'张三'
,N
'语文'
union?
all
'数学'
all
'英语'
all
'物理'
all
'李四'
all
all
all
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
),
[物理]=
'物理'?
'英语'?
'语文'?
)?
from?
Class?
by?
[Student]
?
GO
动态:
?
@s?nvarchar(4000)
@s=
isnull
(@s+
)+quotename([Course])?
[Course]
'select?*?from?Class?pivot?(max([Score])?for?[Course]?in('
'))b'
)
?
生成静态:
*?
from?
Class?
pivot?
(
([Score])?
for?
[Course]?
in
([数学],[物理],[英语],[语文]))b
?
生成格式:
/*
Student?数学??????????物理??????????英语??????????语文
-------?-----------?-----------?-----------?-----------
李四??????77??????????85??????????65??????????65
张三??????87??????????90??????????82??????????78
?
(2?行受影响)
*/
?
------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)
?
--2000方法:
动态:
?
@s?nvarchar(4000)
''
'?then?[Score]?else?0?end)'
[Course]
)
--加多一列(学科平均分用avg([Score]))
?
生成动态:
?
select?
[总成绩]=
sum
([Score])?
--加多一列(学科平均分用avg([Score]))
from?
Class?
[Student]
?
go
?
--2005方法:
?
动态:
?
@s?nvarchar(4000)
[Course]?
--isnull(@s+',','')?去掉字符串@s中第一个逗号
'select?[Student],[总成绩]?from?(select?*,[总成绩]=sum([Score])over(partition?by?[Student])?from?Class)?a?
pivot?(max([Score])?for?[Course]?in('
'))b?'
)
?
生成静态:
?
select?
from?
*,[总成绩]=
([Score])over(partition?
[Student])?
Class)?a?
--平均分时用avg([Score])
pivot?
?
生成格式:
?
/*
Student?数学??????????物理??????????英语??????????语文??????????总成绩
-------?-----------?-----------?-----------?-----------?-----------
李四??????77??????????85??????????65??????????65??????????292
张三??????87??????????90??????????82??????????78??????????337
?
(2?行受影响)
*/
?
go
?
--2、列转行
-->?-->?(Roy)生成測試數據
??
null
Class
Go
)
Class
all
Go
?
--2000:
?
动态:
?
@s?nvarchar(4000)
@s=
'?union?all?'
+quotename(
Name
--isnull(@s+'?union?all?','')?去掉字符串@s中第一个union?all
+
'?from?Class'
syscolumns?
where?
ID=object_id(
)?
and?
Name?
not?
'Student'
--排除不转换的列
order?
Colid
'select?*?from?('
')t?order?by?[Student],[Course]'
--增加一个排序
?
生成静态:
*?
all?
all?
all?
Class)t?
?
go
--2005:
?
动态:
?
@s?nvarchar(4000)
)+quotename(
)
)?
Colid
'select?Student,[Course],[Score]?from?Class?unpivot?([Score]?for?[Course]?in('
)
?
go
select?
Student,[Score]?
from?
Class?
unpivot?
([Score]?
|