Sql 动态行转列

最新推荐文章于 2023-11-17 11:53:58 发布

weixin_30707875

最新推荐文章于 2023-11-17 11:53:58 发布

阅读量308

点赞数

原文链接：http://www.cnblogs.com/hanjun0612/p/9779849.html

版权

SQL动态行转列

关键字：pivot

复杂例子：（使用了连表查询）

看一下图

语句：

--Student
select * from [dbo].[Student]
--StudentScore
select * from [dbo].[StudentScore]

--查询每天的加减分情况
DECLARE @sql VARCHAR(8000),@col varchar(1000)
set @col=(select distinct '['+cast(createTime as nvarchar(100))+'],' from StudentScore for xml path(''))
set @col=LEFT(@col,LEN(@col)-1)
SET @sql = 'select *  
from (select s.name,s.score,grade,createTime from StudentScore ss
		join Student s on ss.sid=s.id
	) as tab  
pivot(sum(grade) for createTime in('+@col+'))as p  '
EXEC(@sql)

简单例子：

给一个静态的，并且值查询StudentScore表

select *  
from 
	(select [sid],grade,createTime from StudentScore) as tab  
pivot(sum(grade) for createTime in([2017-09-04],[2017-09-05],[2017-09-06]))as p

转载于:https://www.cnblogs.com/hanjun0612/p/9779849.html