Sql动态⾏转列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