InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 14:59:47 GMT20http://www.sqlservercentral.com/Forums/Topic1380364-1292-1.aspxThank you all for your input. It's working like a charm!Mon, 05 Nov 2012 14:01:27 GMTedgar58http://www.sqlservercentral.com/Forums/Topic1380364-1292-1.aspx[quote][b]edgar58 (11/2/2012)[/b][hr]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,Program5However, 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)BeginSet @ColumnList = @ColumnList+'Program1,'EndIf Exists(Select Program2 from Table where Program2 is not null)BeginSet @ColumnList = @ColumnList+'Program2,'End...And so on, Then:BeginDeclare @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,EndColumnHowever, 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! [/quote]For starters, you're missing a comma after the DOB column and @column list has no leading comma.Fri, 02 Nov 2012 18:16:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1380364-1292-1.aspxJust 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.Fri, 02 Nov 2012 08:32:54 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1380364-1292-1.aspxHi, 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,Program5However, 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)BeginSet @ColumnList = @ColumnList+'Program1,'EndIf Exists(Select Program2 from Table where Program2 is not null)BeginSet @ColumnList = @ColumnList+'Program2,'End...And so on, Then:BeginDeclare @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,EndColumnHowever, 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! Fri, 02 Nov 2012 07:37:40 GMTedgar58