how to count 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

General SQL Server Forums

New to SQL Server Programming

how to count columns

Author

Topic

raihan
Starting Member

3 Posts

Posted - 2009-04-13 : 20:23:54

Hi,

I'm a newbie so could someone help me with this please?

I've a SQL Table which has data like this:

Date Value1 Value2 Value3 ... ValueN
X 10 20 30
Y 50 NULL 60
Z NULL 40 NULL

I need to find out for each row > the number of columns that has values in it. So for example, for row X the result should be 3, for row Y, the result should be 2 (as Value2 is NULL), for row Z the result should be 1, and so on. And also, if the result is less than 6 then I need to show output as NULL > so it will only display results if the output is AT LEAST 6.

How can I do this? Can anyone help me please?

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-14 : 02:35:06

Raihan,

Yes. Try the following & let me know.

regards,
Anil Kumar.

DECLARE @VC TABLE
( Dat Datetime, Value1 varchar(10), Value2 varchar(10), Value3 Varchar(10), Value4 varchar(10),
value5 varchar(10), value6 varchar(10), value7 varchar(10)
)
INSERT INTO @vc
SELECT '2009-02-01 01:12:10.000','TT','Aa','TT','Aa','TT','Aa','' UNION ALL
SELECT '2009-02-01 01:12:11.000','SS','A','TT','Aa','TT','','' UNION ALL
SELECT '2009-02-01 01:12:12.000','FFF','A','TT','Aa','TT','Aa','AA' UNION ALL
SELECT '2009-02-01 01:12:13.000','AI','AA','TT','Aa','NN','AA','' UNION ALL
SELECT '2009-02-01 01:12:14.000','GI','AA','TT','Aa','TT','Aa','' UNION ALL
SELECT '2009-02-01 01:12:15.000','','ADC','','','','','AA' UNION ALL
SELECT '2009-02-01 01:12:16.000','RC','','Aa','TT','Aa','','' UNION ALL
SELECT '2009-02-01 01:12:17.000','','','','','','',''

Select * from
(select Dat, Value1, Value2, Value3, Value4, Value5, Value6, Value7,
THH =
Case when Value1 != '' or Value1 is null then 1 else 0 end +
Case when Value2 != '' or Value1 is null then 1 else 0 end +
Case when Value3 != '' or Value1 is null then 1 else 0 end +
Case when Value4 != '' or Value1 is null then 1 else 0 end +
Case when Value5 != '' or Value1 is null then 1 else 0 end +
Case when Value6 != '' or Value1 is null then 1 else 0 end +
Case when Value7 != '' or Value1 is null then 1 else 0 end
From
@VC)A
Where A.THH >= 6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 02:38:08

SELECT *
FROM Table1
WHERE ABS(COALESCE(SIGN(Col1), 0)) +
ABS(COALESCE(SIGN(Col2), 0)) +
ABS(COALESCE(SIGN(Col3), 0)) +
ABS(COALESCE(SIGN(ColN), 0)) >= 6

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources