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!