unpivot a table using column_name function

Question

I have a table which has been designed to have columns named the same as a value I need to reference it with. I know I need to unpivot the table and have got it working if I manually type all the column names, however this table keeps getting new columns added to it so I want to capture all the columns in the unpivot rather than script them manually.

I can get the column names using the column_name function and was wondering if this can be added to the unpivot at all, ive been playing around with it and its not looking possible at the moment to me so thought id check to see if there were any other suggestions.

Sadly I cant redesign the table with where the column names keep getting added to although that would be the ideal solution.

select Day, Rota, RotaTemplate
from table1 t1
unpivot
(
  Rota
  for RotaTemplate in (select Column_name
                        from INFORMATION_SCHEMA.COLUMNS
                        where TABLE_NAME = 'table1')
) unpiv;

Answer 1

You are not allowed to do this. You need to build dynamic SQL statement like follows:

DECLARE @DynamicSQL NVARCHAR(MAX)

SET @DynamicSQL = N'select Day, Rota, RotaTemplate' + CHAR(10) + 
                 'from table1 t1'+ CHAR(10) + 
                 'unpivot' + CHAR(10) + 
                 '(' + CHAR(10) + CHAR(9) + 
                    'Rota for RotaTemplate in (' 
                    +  
                     STUFF
                    (
                        (
                            SELECT ',[' + [COLUMN_NAME] + '] '
                            FROM [INFORMATION_SCHEMA].[COLUMNS]
                            WHERE [TABLE_NAME] = 'table1'
                            FOR XML PATH('')
                        )
                        ,1
                        ,1
                        ,''
                    )
                    +')'  + CHAR(10) +
                 ') unpiv;'

EXEC sp_executesql @DynamicSQL