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:
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 ''
(Select Program1 from Table where Program1 is not null)
Set @ColumnList = @ColumnList+'Program1,'
(Select Program2 from Table where Program2 is not null)
Set @ColumnList = @ColumnList+'Program2,'
...And so on, Then:
Declare @Command as varchar(500)
Set @Command = 'Select Account,Fname,Lname,DOB' + @ColumnList + ' EndColumn Into NewTable from Table'
The "EndColumn" is added to account for the last "," in the @ColumnList variable.
The resulting table should contain:
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!