首页 MsSql sqlserver 行列互转实现小结

sqlserver 行列互转实现小结

列转行比较经典,需要的朋友可以参考下。

<div class=”codetitle”><a style=”CURSOR: pointer” data=”17940″ class=”copybut” id=”copybut17940″ onclick=”doCopy(‘code17940’)”> 代码如下:<div class=”codebody” id=”code17940″>
–行列互转
/**
以学生成绩为例子,比较形象易懂 整理人:中国风(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 行受影响)
/

本文来自网络,不代表青岛站长网立场。转载请注明出处: https://www.0532zz.com/html/shujuku/mssql/20200925/10340.html
上一篇
下一篇

作者: dawei

【声明】:青岛站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐

返回顶部