Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing Null Columns Expand / Collapse
Author
Message
Posted Friday, November 2, 2012 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 3:53 PM
Points: 5, Visits: 64
Hi, I'm trying to remove null columns. The columns that need to be removed are not always the same; I run several queries for certain data sets and the resulting columns can vary.

For example I have a table that contains:
Account,
Fname,
Lname,
DOB,
Program1,
Program2,
Program3,
Program4,
Program5

However, Program2 and Program5 or any other program may contain all null values. So I am running the following query to create a new table that only contains the columns that contain data.

Declare @ColumnList as Varchar(100)
Set @ColumnList as ''
If Exists
(Select Program1 from Table where Program1 is not null)
Begin
Set @ColumnList = @ColumnList+'Program1,'
End
If Exists
(Select Program2 from Table where Program2 is not null)
Begin
Set @ColumnList = @ColumnList+'Program2,'
End
...And so on, Then:

Begin
Declare @Command as varchar(500)
Set @Command = 'Select Account,Fname,Lname,DOB' + @ColumnList + ' EndColumn Into NewTable from Table'
exec (@Command)

The "EndColumn" is added to account for the last "," in the @ColumnList variable.

The resulting table should contain:
Account,
Fname,
Lname,
DOB,
Program1,
Program3,
Program4,
EndColumn

However, I am getting an error for incorrect syntax next to "Table" when @Command runs. All help or alternate way to remove columns is appreciated. Thanks!
Post #1380364
Posted Friday, November 2, 2012 8:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
Just output the content of your @Command before executing so you can see the built SQL statement.
You can debug it and find what is wrong with it.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1380408
Posted Friday, November 2, 2012 6:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
edgar58 (11/2/2012)
Hi, I'm trying to remove null columns. The columns that need to be removed are not always the same; I run several queries for certain data sets and the resulting columns can vary.

For example I have a table that contains:
Account,
Fname,
Lname,
DOB,
Program1,
Program2,
Program3,
Program4,
Program5

However, Program2 and Program5 or any other program may contain all null values. So I am running the following query to create a new table that only contains the columns that contain data.

Declare @ColumnList as Varchar(100)
Set @ColumnList as ''
If Exists
(Select Program1 from Table where Program1 is not null)
Begin
Set @ColumnList = @ColumnList+'Program1,'
End
If Exists
(Select Program2 from Table where Program2 is not null)
Begin
Set @ColumnList = @ColumnList+'Program2,'
End
...And so on, Then:

Begin
Declare @Command as varchar(500)
Set @Command = 'Select Account,Fname,Lname,DOB' + @ColumnList + ' EndColumn Into NewTable from Table'
exec (@Command)

The "EndColumn" is added to account for the last "," in the @ColumnList variable.

The resulting table should contain:
Account,
Fname,
Lname,
DOB,
Program1,
Program3,
Program4,
EndColumn

However, I am getting an error for incorrect syntax next to "Table" when @Command runs. All help or alternate way to remove columns is appreciated. Thanks!


For starters, you're missing a comma after the DOB column and @column list has no leading comma.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380649
Posted Monday, November 5, 2012 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 3:53 PM
Points: 5, Visits: 64
Thank you all for your input. It's working like a charm!
Post #1381260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse