select non zero columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select non zero columns

Author

Topic

turbo
Starting Member

19 Posts

Posted - 2011-04-14 : 09:08:30

I have a table
sno | col1 | col2
1 12 0
2 3 0
3 2 0

i have to show only columns which are not having all zeros...
For eg : above table i have to show only sno ,col1 ...
Note: Values inside the columns are not static... (if col1 has all zeros i should not show them ,and show col2 if it have one value more than zero )

Pls help
Thks in Advance ...

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-14 : 09:24:14

Do you mean something like this?
/*
--Prepare table
--SELECT 1 as sno, 12 as col1, 0 as col2
--INTO #table
--UNION ALL
--SELECT 2, 3, 0
--UNION ALL
--SELECT 3, 2, 0
*/

SELECT
sno
,CASE WHEN col1 <> 0 THEN col1 END AS col1
,CASE WHEN col2 <> 0 THEN col1 END AS col2
FROM #table

If you're working with SSRS or a web app, you probably should do the work in the presentation layer, i.e. suppress if the sum of the column = 0

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-14 : 09:27:36

Think you need to be a bit clearer about the requirements

if exists (select * from tbl where col1 <> 0 and col2 <> 0)
select sno,col1,col2
from tbl
else if exists (select * from tbl where col1 <> 0)
select sno,col1
from tbl
else if exists (select * from tbl where col2 <> 0)
select sno,col2
from tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

turbo
Starting Member

19 Posts

Posted - 2011-04-14 : 09:34:08

--SELECT 1
declare @abc table (sno int identity , ab int , bc int)
insert into @abc
select 12 ,0
union
select 23 ,0
union
select 55 ,0

select * from @abc
delete from @abc
go
--SELECT 2
declare @abc table (sno int identity , ab int , bc int)
insert into @abc
select 0 ,23
union
select 0 ,34
union
select 0 ,66
select * from @abc

Result :
For the first select i should get only cols : sno and ab (not bc)
For the second select i should get only cols : sno and bc (not ab)

turbo
Starting Member

19 Posts

Posted - 2011-04-14 : 09:41:34

thks Nigelrivett
..
Works good ,

But is ter any other solution wer there are N number of columns .. any dynamic query which can be used ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-14 : 10:55:41

Good grief - are you sure you want to go anywhere near this. I thought I was joking.
I haven't run any of this so will probably have errors.

declare @t table (colname varchar(10))
insert @t select column_name from INFORMATION_SCHEMA.SCHEMATA.column where table_name = 'mytable' and column_name like 'col%'

declare @i int
declare @sql nvarchar(1000)
declare @col varchar(10)
select @col=''
while @col < (select max(colname) from @t)
begin
select @col = MIN(colname) from @t where colname > @col)

select @i = 0
select @sql = 'select @i = 1 where exists (select * from mytable where ' + @col + ' is not null)'
exec sp_executesql @sql, N'@i int out', @i out
if @i <> 1 delete @t where colname = @col
end

select @sql = stuff (
(
select ',' + colname
from @t
for xml path('')
)
,1,1,'')

select @sql = 'select sno,' + @sql + ' from mytbl'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources