if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] nvarchar(4),[time] nvarchar(4),[score] numeric(2,1))
insert [huang]
select 1,N'张三',N'1月',7.5 union all
select 1,N'张三',N'2月',8.5 union all
select 1,N'张三',N'3月',9.5 union all
select 2,N'李四',N'1月',7.1 union all
select 2,N'李四',N'2月',5.5 union all
select 2,N'李四',N'3月',7.5
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([time])+N'=max(case when [time]=N'+quotename([time],'''')+N' then [Score] else 0.0 end)'
from [huang] group by [time]
EXEC('select [id],[NAME]'+@s+' from [huang] group by [id],[NAME]')
行列转换的例子(学习笔记)
最新推荐文章于 2024-07-10 23:40:31 发布