34,575
社区成员
发帖
与我相关
我的任务
分享
create table device
(
D_ID int IDENTITY(1,1) NOT NULL,
D_Temp2 nvarchar(50),
}
insert into device values('1')
insert into device values('11')
insert into device values('12')
insert into device values('13')
insert into device values('22')
insert into device values('15')
insert into device values('17')
insert into device values('15')
select N'[型号或规格]' ,max((case when D_ID=N'1' then D_Temp2 else null end )) as [1] ,
max((case when D_ID=N'2' then D_Temp2 else null end )) as [2] ,
max((case when D_ID=N'3' then D_Temp2 else null end )) as [3] ,
max((case when D_ID=N'4' then D_Temp2 else null end )) as [4] ,
max((case when D_ID=N'5' then D_Temp2 else null end )) as [5] ,
max((case when D_ID=N'6' then D_Temp2 else null end )) as [6] ,
max((case when D_ID=N'7' then D_Temp2 else null end )) as [7] ,
max((case when D_ID=N'8' then D_Temp2 else null end )) as [8] from device
from device d where not exists (select 1 from device where D_Temp2=d.D_Temp2 and and col2=t.col2 and ... and D_ID<d.D_ID)
from device d where not exists (select 1 from device where D_Temp2=d.D_Temp2 and D_ID<d.D_ID)
declare @sql varchar(8000)
select @sql=isnull(@sql+',','') + 'max((case when D_ID='+ltrim(D_ID)+' then D_Temp2 end)) as ['+ltrim(D_ID)+']'
from device d where not exists (select 1 from device where D_Temp2=d.D_Temp2 and D_ID<d.D_ID)
exec ('select N''[型号或规格]'','+@sql+' from device')
select N'[型号或规格]' ,
max((case when D_ID=N'1' then D_Temp2 else null end )) as [1] ,
max((case when D_ID=N'2' then D_Temp2 else null end )) as [2] ,
max((case when D_ID=N'3' then D_Temp2 else null end )) as [3] ,
max((case when D_ID=N'4' then D_Temp2 else null end )) as [4] ,
max((case when D_ID=N'5' then D_Temp2 else null end )) as [5] ,
max((case when D_ID=N'6' then D_Temp2 else null end )) as [6] ,
max((case when D_ID=N'7' then D_Temp2 else null end )) as [7] ,
max((case when D_ID=N'8' then D_Temp2 else null end )) as [8] from
(select max(D_ID) as D_ID,D_Temp2 from device group by D_Temp2) temp
declare @cols1 Nvarchar(2000)
set @cols1=''
select @cols=@cols+N',max((case when D_ID=N'''+CAST(D_ID AS NVARCHAR(10))+N''' then D_Temp2 else null end )) as ['+CAST(D_ID AS NVARCHAR(10))+'] '
from (SELECT D_ID FROM device ) AS T
exec(N'select N''[型号]'' '+@cols1+N' from device)
select N'[型号或规格]' ,
max((case when D_Temp2=N'1' then D_Temp2 else null end )) as [1] ,
max((case when D_Temp2=N'11' then D_Temp2 else null end )) as [2] ,
max((case when D_Temp2=N'12' then D_Temp2 else null end )) as [3] ,
max((case when D_Temp2=N'13' then D_Temp2 else null end )) as [4] ,
max((case when D_Temp2=N'22' then D_Temp2 else null end )) as [5] ,
max((case when D_Temp2=N'15' then D_Temp2 else null end )) as [6] ,
max((case when D_Temp2=N'17' then D_Temp2 else null end )) as [7]
from device