| Author |
 Topic  |
|
|
turbo
Starting Member
19 Posts |
 Posted - 04/14/2011 : 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
United Kingdom
226 Posts |
Posted - 04/14/2011 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3385 Posts |
Posted - 04/14/2011 : 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 - 04/14/2011 : 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 - 04/14/2011 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3385 Posts |
Posted - 04/14/2011 : 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. |
|
|
| |
Topic |
|
|
|
select non zero columns