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

2011-08-11 04:08:14 165 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

实现直流高电压（100Vdc～1000Vdc）检测的采样电路（隔离方案）运放U3构成射极跟随器，用来提高检测电路的输出带载能力，隔离前级输出电路和后级处理电路，最终达到提升电压采样精度的目的；（5）运放U1～U3：如果输入直流母线电压较低，经过电流传输、光电感应和电流电压变换后，最后的输出电压幅度可能会比较小，在运放端甚至会出现截止失真（下限截止），同时，运放的失调电压、偏置电流对电压采样精度也有很大影响，因此，选择运放型号为：OPA2171AIDR，该运放属于高精度型，输入失调电压典型值为0.25mV，输入偏置电流小于15pA，基本可以满足采样精度的要求。阅读详情

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

...全文

PLC计数器避坑指南：用C0实现5次循环自动停机（含D寄存器替代方案）本文深入解析三菱PLC中C0计数器的实战应用技巧，包括循环控制实现和D寄存器替代方案，帮助工程师避开常见陷阱。涵盖计数器工作原理、硬件接线要点、自复位逻辑实现及高速场景处理，适用于工业自动化领域的精确循环计数控制需求。阅读详情

dovei

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

chtzhking 2011-08-12

打赏
举报





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

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

dovei 2011-08-12

打赏
举报

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

--小F-- 2011-08-11

打赏
举报

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

chtzhking 2011-08-11

打赏
举报



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

youjq2011 2011-08-11

打赏
举报

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

老潘 2011-08-11

打赏
举报



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

*/

dovei 2011-08-11

打赏
举报

高手何在!

dovei 2011-08-11

打赏
举报

坐沙发的哎啥

zlblog 2011-08-11

打赏
举报

难度系数太高了。

欢迎光临我的博客

纵向判断了都。

chuanzhang5687 2011-08-11

打赏
举报

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

快溜 2011-08-11

打赏
举报

哎~~

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

相关推荐