27,579
社区成员
发帖
与我相关
我的任务
分享
drop table #aa
create table #aa (id int,房间号 varchar(10),设备名称 varchar(10),设备功率 varchar(10),设备数量 varchar(10))
insert into #aa values('1','1','A','100','1')
insert into #aa values('2','1','B','30','20')
insert into #aa values('3','1','C','100','1')
insert into #aa values('4','2','A','30','1')
insert into #aa values('5','2','B','100','20')
insert into #aa values('6','2','C','30','1')
insert into #aa values('7','2','D','100','1')
insert into #aa values('8','2','E','30','1')
insert into #aa values('9','3','A','100','1')
insert into #aa values('10','4','A','30','1')
insert into #aa values('11','4','B','100','1')
insert into #aa values('12','4','C','30','1')
insert into #aa values('13','4','D','100','1')
insert into #aa values('14','4','E','30','1')
insert into #aa values('15','4','F','100','1')
--select * from #aa
declare @sql nvarchar(max),@icnt int,@i int
select @sql ='',@i =1, @icnt = max(c) from (select count(1) as c from #aa group by [房间号]) a
set @sql = ' SELECT [房间号]+''(房间号)'' '
while @i <= @icnt
begin
if @i = 1 set @sql = @sql + ', col'+ltrim(@i)+'= max( CASE WHEN ASCII([设备名称]) = ' + ltrim(64+@i) +' THEN [设备功率]+ ''(A的功率)'' ELSE '''' END ) '
else if @i = 2 set @sql = @sql + ', col'+ltrim(@i)+'= max( CASE WHEN ASCII([设备名称]) = ' + ltrim(64+@i) +' THEN [设备数量]+ ''(设备B的数量)'' ELSE '''' END ) '
else set @sql = @sql + ', col'+ltrim(@i)+'= max( CASE WHEN ASCII([设备名称]) = ' + ltrim(64+@i) +' THEN [设备名称]+ ''(设备名)'' ELSE '''' END ) '
set @i = @i + 1
end
set @sql = @sql +' from #aa group by [房间号] '
--print @sql
exec (@sql)
/*
col1 col2 col3 col4 col5 col6
------------------ ------------------- ----------------------- ------------------ ------------------ ------------------ ------------------
1(房间号) 100(A的功率) 20(设备B的数量) C(设备名)
2(房间号) 30(A的功率) 20(设备B的数量) C(设备名) D(设备名) E(设备名)
3(房间号) 100(A的功率)
4(房间号) 30(A的功率) 1(设备B的数量) C(设备名) D(设备名) E(设备名) F(设备名)
*/
create table #aa (id int,房间号 varchar(10),设备名称 varchar(10),设备功率 varchar(10),设备数量 varchar(10))
insert into #aa values('1','1','A','100','1')
insert into #aa values('2','1','B','30','20')
insert into #aa values('3','1','C','100','1')
insert into #aa values('4','2','A','100','1')
insert into #aa values('5','2','B','100','20')
insert into #aa values('6','2','C','100','1')
insert into #aa values('7','2','D','100','1')
insert into #aa values('8','2','E','100','1')
insert into #aa values('9','3','A','100','1')
insert into #aa values('10','4','A','100','1')
insert into #aa values('11','4','B','100','1')
insert into #aa values('12','4','C','100','1')
insert into #aa values('13','4','D','100','1')
insert into #aa values('14','4','E','100','1')
insert into #aa values('15','4','F','100','1')
--select * from #aa
declare @sql nvarchar(max),@icnt int,@i int
select @sql ='',@i =1, @icnt = max(c) from (select count(1) as c from #aa group by [房间号]) a
set @sql = ' SELECT [房间号]+''(房间号)'' '
while @i <= @icnt
begin
if @i = 1 set @sql = @sql + ', col'+ltrim(@i)+'= max( CASE WHEN ASCII([设备名称]) = ' + ltrim(64+@i) +' THEN [设备功率]+ ''(A的功率)'' ELSE '''' END ) '
else if @i = 2 set @sql = @sql + ', col'+ltrim(@i)+'= max( CASE WHEN ASCII([设备名称]) = ' + ltrim(64+@i) +' THEN [设备功率]+ ''(设备B的数量)'' ELSE '''' END ) '
else set @sql = @sql + ', col'+ltrim(@i)+'= max( CASE WHEN ASCII([设备名称]) = ' + ltrim(64+@i) +' THEN [设备名称]+ ''(设备名)'' ELSE '''' END ) '
set @i = @i + 1
end
set @sql = @sql +' from #aa group by [房间号] '
--print @sql
exec (@sql)
/*
col1 col2 col3 col4 col5 col6
------------------ ------------------- ----------------------- ------------------ ------------------ ------------------ ------------------
1(房间号) 100(A的功率) 30(设备B的数量) C(设备名)
2(房间号) 100(A的功率) 100(设备B的数量) C(设备名) D(设备名) E(设备名)
3(房间号) 100(A的功率)
4(房间号) 100(A的功率) 100(设备B的数量) C(设备名) D(设备名) E(设备名) F(设备名)
(4 row(s) affected)
*/
......
CASE [设备名称] WHEN N'设备A' THEN [功率] WHEN N'设备B' THEN [数量] ELSE [设备名称] END AS [字段别名]
......