Interesting Question HELP - 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

General SQL Server Forums

New to SQL Server Programming

Interesting Question HELP

Author

Topic

vishnu.cm
Starting Member

7 Posts

Posted - 2008-10-15 : 14:40:55

Can some body help me with this

If i have a result set like this

Col1 Col2 Col3 Col4 Col5 Col6
------------------------------------------------
A NULL Spec1 C NULL Plan2
B Cap1 Spec2 C MC Plan1
C Cap1 NULL C MC Plan3

Now how can i possibily get the column count for each row lets say i need the count of no:of column per row which is not null.

Some thing like this:

Col1 Col2 Col3 Col4 Col5 Col6 Col Count
------------------------------------------------ ---------
A NULL Spec1 C NULL Plan2 4
B Cap1 Spec2 C MC Plan1 6
C Cap1 NULL C MC Plan3 5

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-15 : 14:55:48

create table #tmp (
Col1 varchar(20),
Col2 varchar(20),
Col3 varchar(20),
Col4 varchar(20),
Col5 varchar(20),
Col6 varchar(20)
)

insert into #tmp
select 'A', NULL , 'Spec1', 'C', NULL, 'Plan2' Union all
select 'B', 'Cap1', 'Spec2', 'C', 'MC', 'Plan1' Union all
select 'C', 'Cap1', NULL, 'C', 'MC', 'Plan3'

select
col1, col2, col3, col4, col5, col6,
(case when col1 is null then 0 else 1 end ) +
(case when col2 is null then 0 else 1 end ) +
(case when col3 is null then 0 else 1 end ) +
(case when col4 is null then 0 else 1 end ) +
(case when col5 is null then 0 else 1 end ) +
(case when col6 is null then 0 else 1 end )
from #tmp

drop table #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources