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

[dovei]

如题:
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

[快溜]

哎~~

[chuanzhang5687]

这~~[Quote=引用 1 楼 ssp2009 的回复:]
哎~~
[/Quote]

[zlblog]

难度系数太高了。

欢迎光临我的博客

纵向判断了都。

[dovei]

坐沙发的哎啥

[dovei]

高手何在!

[老潘]

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
*/

[youjq2011]

行转列
然后字段加起来不等0
然后再转行

[chtzhking]

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 @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 行受影响)

[--小F--]

全部加起来不等于0的行就是所需要的

[dovei]

>#8
方法很不错.有没有更通用一点的方法

[chtzhking]

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,0

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

结果是一样的，只是和列数没有关系