Return cols with values - 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

Old Forums

CLOSED - General SQL Server

Return cols with values

Author

Topic

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-12-18 : 07:25:47

Hi I am having some trouble trying to figure out this sql query or should I be trying to do this with c# and not sql, I have a database table with 20 columns but I only want to return the columns that have values in at least one of their rows, so if a column consists entirely of null values I do not want to return it. I’m just wondering if anyone has any help or advice with the sql or whether or not its possible.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-18 : 07:29:51

It's not possible!

You can exclude rows based on certain criteria, but not the columns!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 07:40:23

I think it is possible with the help of Dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-18 : 07:53:10

Yes, but surely you would not advise using it, would you?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 08:20:25

Obsolutely not.
But the question is still interesting.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 09:37:00

quote:
Originally posted by tadhg88

Hi I am having some trouble trying to figure out this sql query or should I be trying to do this with c# and not sql, I have a database table with 20 columns but I only want to return the columns that have values in at least one of their rows, so if a column consists entirely of null values I do not want to return it. I’m just wondering if anyone has any help or advice with the sql or whether or not its possible.

1 Why do you need this?
2 I think you want to show data in GRID. Check if all rows have values in all columns using While loop. If not you can hide that column. I dont know C# and not able to help you in code

Madhivanan

Failing to plan is Planning to fail

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-12-18 : 09:43:35

yes thats what i tried to do originally but i struggled to do it using c# so i said i would try and fidn out if it was better to do it using sql or c#

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 10:04:50

Dont do it in sql, post your question at C# forums

Madhivanan

Failing to plan is Planning to fail

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2006-12-18 : 11:50:34

ok thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-18 : 14:36:51

Also, to avoid the need for hacks like this, consider learning about normalizing your database.
A good database design eliminates the need for things like this; columns returning should always be constants and variations in the data itself should be represented as varying numbers of rows, never columns.

see: http://www.datamodel.org/NormalizationRules.html

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-18 : 15:07:16

It's gonna get a tad messy with this one


SET NOCOUNT ON
DECLARE @sql varchar(8000)

set @sql = ''

SELECT @SQL = @SQL +
'SELECT CASE WHEN FullCount - NullCount <> 0 Then '''+Column_Name+ ','''+ ' ELSE '''' END AS ColName '
+'FROM ( SELECT * FROM ' 
+'  (SELECT COUNT(*) AS NullCount FROM '+TABLE_NAME+' WHERE '+ Column_name+' IS NULL) as xxx '
+', (SELECT COUNT(*) AS FullCount FROM '+TABLE_NAME+') as yyy) AS ZZZ UNION ALL '
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION

SELECT @sql = LEFT(@sql,LEN(@sql)-LEN('UNION ALL '))
EXEC(@sql)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources