• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

sqlserver 行列互转实现小结_sqlserver

sqlserver 搞代码 3年前 (2018-06-17) 61次浏览 已收录 0个评论
复制代码 代码如下:
–行列互转
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂

整理人:中国风(Roy)

http://www.gaodaima.com/?p=37480sqlserver 行列互转实现小结_sqlserver

日期:2008.06.06
******************************************************************************************************************************************************/

–1、行互列
–> –> (Roy)生成y

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 @[email protected]+’,’+quotename([Course])+’=max(case when [Course]=’+quotename([Course],””)+’ then [Score] else 0 end)’
from Class group by[Course]
exec(‘select [Student]’[email protected]+’ 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(‘[email protected]+’))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 @[email protected]+’,’+quotename([Course])+’=max(case when [Course]=’+quotename([Course],””)+’ then [Score] else 0 end)’
from Class group by[Course]
exec(‘select [Student]’[email protected]+’,[总成绩]=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+’,’,”) [email protected]
exec(‘select [Student],’[email protected]+’,[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in(‘[email protected]+’))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)生成y

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 ‘,”) [email protected] all
+’,[Score]=’+quotename(Name)+’ from Class’
from syscolumns where ID=object_id(‘Class’) and Name not in(‘Student’)–排除不转换的列
order by Colid
exec(‘select * from (‘[email protected]+’)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(‘[email protected]+’))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 行受影响)
*/

欢迎大家阅读《sqlserver 行列互转实现小结_sqlserver》,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址