Removing Null Columns

Question

Post reply

Removing Null Columns

edgar58

Valued Member

Points: 51
More actions
November 2, 2012 at 7:37 am

#270440

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edgar58 Valued Member Points: 51 More actions · Answer 3

Thank you all for your input. It's working like a charm!