SQL 去除重复项

[kevin87923]

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

请问要怎么写去除重复的数据？

[jwdream2008]

需求不明啊！

[Roni]

没看明白，怎么重复了？

[SQLCenter]

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

[SQLCenter]

这个要动态才能实现的

[kevin87923]

忘记说了。这是个列转行。

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)

[tashiwoweiyi]

是动态生成的吧

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

[tashiwoweiyi]

列转行的例子

[kevin87923]

嗯。 我想查出来的数据不要重复的。可以实现吗？

[SQLCenter]

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')

[kevin87923]

还有个问题， 我有多列数据。都是列转行的。

from device d where not exists (select 1 from device where D_Temp2=d.D_Temp2 and D_ID<d.D_ID)

这要怎么改呢

[SQLCenter]

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)

[kevin87923]

条件这样写，就没有去除重复数据了。

[SQLCenter]

不理解你的重复是什么意思
重复必然和字段挂钩，1个字段不重复，N个字段不重复，完全是不同的结果。

[kevin87923]

就是每列数据都会有重复的数据。