有一表A.有很多列C1~C200.如何取到不为空或0的列

Question

如题:
id C1 C2 C3 C4 C5 C6 C7 C8
1 0 0 11 0 0 0 0 0
2 11 0 0 0 0 0 0 0
3 11 0 0 0 0 0 0 11
4 0 0 0 11 0 0 0 0
5 0 0 0 0 0 0 0 11
6 11 0 0 0 0 0 0 0所需结果:
id C1 C3 C4 C8
1 0 11 0 0
2 11 0 0 0
3 11 0 0 11
4 0 0 11 0
5 0 0 0 11
6 11 0 0 0

Answer 1

create table #t(
id int
,C1 int
,C2 int
,C3 int
,C4 int
,C5 int
,C6 int
,C7 int
,C8 int
);
INSERT into #t
select 1,0,0,11,0,0,0,0,0 union all
select 2,11,0,0,0,0,0,0,0 union all
select 3,11,0,0,0,0,0,0,11 union all
select 4,0,0,0,11,0,0,0,0 union all
select 5,0,0,0,0,0,0,0,11 union all
select 6,11,0,0,0,0,0,0,0
;
declare @Cols nvarchar(100);--1.列转行
WITH t1 as
(
select *
from (
SELECT id, c1, c2, c3, c4, c5, c6, c7, c8
FROM #t
 ) t
UNPIVOT
(Nums FOR Name IN 
        (c1, c2, c3, c4, c5, c6, c7, c8)
) u
)
--2.过滤掉所有数据为0的列
,t2 as
(
SELECT Name
FROM t1
group by Name
having sum(Nums)>0
)
--3.生成可用列字符串
select @Cols=stuff(replace(replace((select Name
from t2 
FOR XML PATH('')),'<Name>',','),'</Name>',''),1,1,'')
;
--执行SQL
EXEC('select id,'+@Cols+' from #t');drop table #t;--结果集
/*
id          c1          c3          c4          c8
----------- ----------- ----------- ----------- -----------
1           0           11          0           0
2           11          0           0           0
3           11          0           0           11
4           0           0           11          0
5           0           0           0           11
6           11          0           0           0
*/

Answer 2

create table #t(
id int
,C1 int
,C2 int
,C3 int
,C4 int
,C5 int
,C6 int
,C7 int
,C8 int
);
INSERT into #t
select 1,0,0,11,0,0,0,0,0 union all
select 2,11,0,0,0,0,0,0,0 union all
select 3,11,0,0,0,0,0,0,11 union all
select 4,0,0,0,11,0,0,0,0 union all
select 5,0,0,0,0,0,0,0,11 union all
select 6,11,0,0,0,0,0,0,0declare @sql varchar(1000)
set @sql=''
select @sql=@sql+b+',' from (
select COUNT(c1) as c,'C1' as b from #t where c1!=0 union all
select COUNT(c2),'C2' from #t where c2!=0 union all
select COUNT(c3),'C3' from #t where c3!=0 union all
select COUNT(c4),'C4' from #t where c4!=0 union all
select COUNT(c5),'C5' from #t where c5!=0 union all
select COUNT(c6),'C6' from #t where c6!=0 union all
select COUNT(C7),'C7' from #t where C7!=0 union all
select COUNT(C8),'C8' from #t where C8!=0) as a  where c!=0
set @sql=substring(@sql,0,len(@sql))
exec('select id,'+@sql+' from #t')
-----------------------------------
id          C1          C3          C4          C8
----------- ----------- ----------- ----------- -----------
1           0           11          0           0
2           11          0           0           0
3           11          0           0           11
4           0           0           11          0
5           0           0           0           11
6           11          0           0           0(6 行受影响)

Answer 3

create table tb(
id int
,C1 int
,C2 int
,C3 int
,C4 int
,C5 int
,C6 int
,C7 int
,C8 int
);
INSERT into tb
select 1,0,0,11,0,0,0,0,0 union all
select 2,11,0,0,0,0,0,0,0 union all
select 3,11,0,0,0,0,0,0,11 union all
select 4,0,0,0,11,0,0,0,0 union all
select 5,0,0,0,0,0,0,0,11 union all
select 6,11,0,0,0,0,0,0,0declare @count int,@i int,@sql varchar(8000),@sql1 varchar(8000)
declare @tb table(a int,b varchar(10))
set @i=1
select @count=COUNT(name) from sys.all_columns where object_id=(
select object_id from sys.tables where name='tb')
while @i<=@count
begin
select @sql='select COUNT('+name+') as c,'''+name+''' as b from tb where '+name+'!=0' from sys.all_columns where object_id=(
select object_id from sys.tables where name='tb') and column_id=@i
insert into @tb
exec(@sql)
set @i=@i+1
end
set @sql1=''
select @sql1=@sql1+b+',' from @tb where b!='id' and a!=0
set @sql1=SUBSTRING(@sql1,0,LEN(@sql1))
exec('select id,'+@sql1+' from tb')结果是一样的，只是和列数没有关系