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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 select non zero columns
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

turbo
Starting Member

19 Posts

Posted - 04/14/2011 :  09:08:30  Show Profile  Reply with Quote
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

United Kingdom
226 Posts

Posted - 04/14/2011 :  09:24:14  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 04/14/2011 :  09:27:36  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

turbo
Starting Member

19 Posts

Posted - 04/14/2011 :  09:34:08  Show Profile  Reply with Quote
--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)
Go to Top of Page

turbo
Starting Member

19 Posts

Posted - 04/14/2011 :  09:41:34  Show Profile  Reply with Quote
thks Nigelrivett
..
Works good ,
But is ter any other solution wer there are N number of columns .. any dynamic query which can be used ?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 04/14/2011 :  10:55:41  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000