How to filter out empty columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to filter out empty columns?

Author

Topic

quarth
Starting Member

1 Post

Posted - 2007-06-07 : 17:41:25

Example: SELECT * FROM TABLENAME returns a table with 5,000 row, and about 1,000 columns. Of these 1,000 columns, many (if not most) have absolutely no data in any of the rows. Can I do a query that will only return a column if it exists in at least one row? Apologies if my lingo is a bit un-DB'ish. :)

Thus, a SELECT statement that would return all all four rows before, but only columns A, B, and C (omit D, since it is blank in all four cases).

Row 1 - TEST,5,1,,
Row 2 - TEST,5,1,,
Row 3 - ,,,,
Row 4 - Test,3,5,,

Thank you!!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-07 : 21:39:07

No.
Fix your database design instead. Returning 1000 columns is just absurd.

e4 d5 xd5 Nf6

phishCoder
Starting Member

4 Posts

Posted - 2007-06-08 : 19:32:09

I think you should definitely take the advice of blindman.

However, this should work...


SELECT * FROM TABLENAME 
where 
not (A = '') and
not (B = '') and
not (C = '') and
not (D = '')

I've had to query some pretty wacked out db's that I had no part of designing. I hope to God that you didn't design this db and if you did, get several books. :-)

J

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-08 : 23:58:39

In Oracle an empty string is equivalent to NULL, but TSQL (correctly) differentiates between the two. So I don't think even the code posted by phish would help. And it also returns every column, where the poster wants to omit columns that only contain nulls.
The code would have to run a dynamic SQL statement constructed by subqueries that checked the viability of every one of those 1000+ columns.

e4 d5 xd5 Nf6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 01:35:43

Why did you have 1000 columns in a single table?
http://www.datamodel.org/NormalizationRules.html

If you cant modify it now, you can make use of this


Select
      column_name+'<>'''' and ' 
from  
      information_schema.columns
where
      table_name='your table'

Then you use those column lists in the query with slight modification

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources