34,588
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#list') is not null drop table #list
select a='a',b='123',c=12 into #list union all
select a='a',b='8',c=128
declare @col nvarchar(1000)='',@colname nvarchar(1000)='',@sql nvarchar(max)=''
select @col=@col+'count(distinct '+[name]+') as '+[name]+','
,@colname=@colname+'['+[name]+'],'
from tempdb.sys.syscolumns a where id=object_id('tempdb..#list')
set @sql='
set @col=''''
select @col=@col+''[''+col+''],''
from (select '+left(@col,len(@col)-1)+'
from #list
) a unpivot (counts for col in('+left(@colname,len(@colname)-1)+'))pt
where counts>=2
'
exec sp_executesql @sql,N'@col nvarchar(1000) out',@col=@col out
set @sql='
select '+left(@col,len(@col)-1)+' from #list
'
exec(@sql)
/*
b c
---- -----------
123 12
8 128
(2 行受影响)
*/