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
请问要怎么写去除重复的数据?
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
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_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
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')
from device d where not exists (select 1 from device where D_Temp2=d.D_Temp2 and D_ID<d.D_ID)
这要怎么改呢