Take for example this example as an illustration so you can see what Î'm trying to do.
This is how the final table of the pivoted information looks like.
Create Table [#Comparative]
(
Branch char(32),
[2004_January] numeric (18,2),
[2005_January] numeric (18,2),
[2006_January] numeric (18,2),
[2007_January] numeric (18,2),
[2008_January] numeric (18,2),
)
INSERT INTO [#Comparative]
VALUES ('Angeles', NULL, 13550.20, 7820.50, NULL, NULL),
('Detroit', NULL, 13550.20, 7820.50, NULL, NULL),
('New York', NULL, 13550.20, 7820.50, NULL, NULL),
('Arizona', NULL, 13550.20, 7820.50, NULL, NULL)
Select * from [#Comparative]
How could i create a procedure or statement to drop the set of columns that contains only NULL values taking into account the columns on the table will be changing as the table is created from other query that takes information of daily sales to group sum(sales) monthly depending on the interval of the selected date.
Dynamically create a SQL statement and then run that command. This script drop set of columns with only null values from a temporary table(passed as parameter in SP).
CREATE PROC dbo.dropColumn
@tempTableName nvarchar(100)
AS
BEGIN
DECLARE @dml nvarchar(max) = N''
SELECT @dml += 'IF (SELECT MIN(' + QUOTENAME(c.name) + ') FROM [dbo].' + QUOTENAME(@tempTableName) + ') IS NULL' + CHAR(13) + CHAR(10) +
'BEGIN' + CHAR(13) + CHAR(10) +
' EXEC (''ALTER TABLE [dbo].' + QUOTENAME(@tempTableName) + ' DROP COLUMN ' + QUOTENAME(c.name) + ''')' + CHAR(13) + CHAR(10) +
'END' + CHAR(13) + CHAR(10)
FROM tempdb.sys.tables t JOIN tempdb.sys.columns c ON t.object_id = c.object_id
WHERE t.object_id = OBJECT_ID('[tempdb].[dbo].' + QUOTENAME(@tempTableName))
--PRINT @dml
EXEC sp_executesql @dml
END
EXEC dbo.dropColumn '#Comparative'
Result:
Branch 2005_January 2006_January
----------------------------------------------------------------
Angeles 13550.20 7820.50
Detroit 13550.20 7820.50
New York 13550.20 7820.50
Arizona 13550.20 7820.50