is it possible ? - 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 2000 Forums

Transact-SQL (2000)

is it possible ?

Author

Topic

cez
Starting Member

37 Posts

Posted - 2002-12-19 : 06:08:33

Hi

I'm creating a temp table on the fly. Some of the columns may be NULL. I need a query that returns all the columns that have all the rows NULL to be able to hide the column in my DataGrid (ASP.NET).

Can you help ? Thanks.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-12-19 : 08:05:15

you should control that at the application level.. the datagrid is very flexible and allows you to do this sort of thing.... you should not look to do this at the db level ... though I think *maybe* you can do something like

CREATE TABLE #temp (a INT, b VARCHAR(10), c MONEY, d VARCHAR(60))

INSERT INTO #temp (a, b, c, d)
SELECT itemID, name, unitCost, owner FROM Items WHERE Enabled = 1

IF NOT EXISTS(SELECT * FROM #temp WHERE a IS NOT NULL)
ALTER TABLE #temp DROP COLUMN a
IF NOT EXISTS(SELECT * FROM #temp WHERE b IS NOT NULL)
ALTER TABLE #temp DROP COLUMN b
IF NOT EXISTS(SELECT * FROM #temp WHERE c IS NOT NULL)
ALTER TABLE #temp DROP COLUMN c
IF NOT EXISTS(SELECT * FROM #temp WHERE d IS NOT NULL)
ALTER TABLE #temp DROP COLUMN d

SELECT * FROM #temp
DROP TABLE #temp

but don't quote me as saying this will work ... :-p

I tried it .. um doesn't quite work so like i said do this at the application level...

Edited by - onamuji on 12/19/2002 08:08:07

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-19 : 08:31:11

To determine if a column has all NULL values:

SELECT
CASE WHEN Col1 is Null THEN 1 ELSE 0 END as Col1NullCount,
CASE WHEN Col2 is Null THEN 1 ELSE 0 END as Col2NullCount,
COUNT(*) as TotalRows
FROM
Table

Any time ColxNullCount = TotalRows, that column has all Null values.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-19 : 22:43:43

Small fix of jsmith8858 solution.

CREATE TABLE #Test
(
TestID int,
Col1 nvarchar(1),
Col2 nvarchar (2)
)

INSERT INTO #Test (TestID, Col1, Col2) VALUES (1,NULL, NULL)
INSERT INTO #Test (TestID, Col1, Col2) VALUES (2,1, NULL)
INSERT INTO #Test (TestID, Col1, Col2) VALUES (3,NULL, NULL)
INSERT INTO #Test (TestID, Col1, Col2) VALUES (4,NULL, NULL)

SELECT
SUM(CASE WHEN Col1 is Null THEN 1 ELSE 0 END) as Col1NullCount,
SUM(CASE WHEN Col2 is Null THEN 1 ELSE 0 END) as Col2NullCount,
COUNT(*) as TotalRows
FROM
#Test

DROP TABLE #Test

Nice looking girl must have walked by and distracted jsmith8858

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-20 : 09:24:15

Valter -- you know me too well!

Yes, SUM()'s might help there.....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources