SQL SERVER 行列转换(动态)

行转列测试数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)
Insert #T
select N'李四',N'语文',60 union all
select N'李四',N'数学',70 union all
select N'李四',N'英语',80 union all
select N'张三',N'语文',90 union all
select N'张三',N'数学',80 union all
select N'张三',N'英语',70
Go
--测试数据结束

动态写法(加上了总分和平均分):

1
2
3
4
5
6
7
8
9
10
11
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select Name'
SELECT  @sql = @sql + ',max(case Subject when ''' + Subject
        + ''' then Score else 0 end)[' + Subject + ']'
FROM    ( SELECT DISTINCT
                    Subject
          FROM      #T
        ) a
SET @sql = @sql
    + ',sum(Score) 总分,cast(avg(Score*1.0) as decimal(18,2)) 平均分 from #T group by Name'
EXEC(@sql)

动态的也可以使用pivot:

1
2
3
4
5
6
7
DECLARE @sql VARCHAR(MAX)
SELECT @sql=isnull(@sql+',','')+Subject FROM #T GROUP BY Subject
SET @sql='select m.* , n.总分, n.平均分 from
(select * from (select * from #T) a pivot (max(Score) for Subject in ('+@sql+')) b) m ,
(select Name,sum(Score)总分, cast(avg(Score*1.0) as decimal(18,2))平均分 from #T group by Name) n
where m.Name= n.Name'
exec(@sql)

列转行的测试数据:

1
2
3
4
5
6
7
8
9
10
--测试数据 
if not object_id(N'Tt') is null
drop table Tt
Go
Create table Tt([姓名] nvarchar(22),[语文] int,[数学] int,[英语] int)
Insert Tt
select N'张三',60,70,80 union all
select N'李四',90,80,70
Go
--测试数据结束

 动态写法:

1
2
3
4
5
6
7
DECLARE @sql VARCHAR(8000)
SELECT @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='
+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from T'
FROM syscolumns
WHERE Name!='姓名' AND ID=object_id('T')--表名tb,不包含列名为姓名的其他列
ORDER BY colid
EXEC(@sql+' order by 姓名')

 同样的动态写法也可以使用unpivot:

1
2
3
4
5
6
7
DECLARE @sql VARCHAR(8000)
SELECT @sql=isnull(@sql+',','')+quotename(Name)
FROM syscolumns
WHERE ID=object_id('T')AND Name NOT IN('姓名')
ORDER BY Colid
SET @sql='select 姓名,[课程],[分数] from T unpivot ([分数] for [课程] in('+@sql+'))b'
exec(@sql)

  

  

  

  

  

  

posted @   每天进步多一点  阅读(4446)  评论(0编辑  收藏  举报
编辑推荐:
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
历史上的今天:
2017-03-06 SQL server 2008定期的备份数据库及删除job
点击右上角即可分享
微信分享提示