Sql-server – Passing column names dynamically to UNPIVOT

sql serversql-server-2008-r2unpivot

I have a table with following data

First         Second        Third         Fourth        Fifth         Sixth
2013-08-20    2013-08-21    2013-08-22    2013-08-23    2013-08-24    2013-08-25

And using UNPIVOT 

SELECT Data
    ,DATENAME(DW, Data) AS DayName
FROM Cal
UNPIVOT(Data FOR D IN (
            First,
            Second,
            Third,
            Fourth,
            Fifth,
            Sixth  )) AS unpvt

I get the following result

Data        DayName
2013-08-20  Tuesday
2013-08-21  Wednesday
2013-08-22  Thursday
2013-08-23  Friday
2013-08-24  Saturday
2013-08-25  Sunday

Now my question is can we pass column names dynamically to the UNPIVOT so that when the columns in the table increases we may not have to alter the statement.

Best Answer

If you are going to have an unknown number of columns that you will need to unpivot, then you will have to look at implementing dynamic SQL.

You can use sys.columns to get the names of all of the columns in your cal table. If you use the following query you will get the list of all of the columns in your table:

select C.name
from sys.columns c
where c.object_id = OBJECT_ID('dbo.cal')

Now you can use this query along with FOR XML PATH to create a comma-separated list of the names to be concatenated to a string to be executed:

select @colsUnpivot 
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.cal') 
           for xml path('')), 1, 1, '')

Finally, you will take this list and place it into your query string to be executed so the full query will look like:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.cal') 
           for xml path('')), 1, 1, '')

set @query 
  = 'select data, datename(dw, data) dayname
     from cal
     unpivot
     (
        data
        for d in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

See SQL Fiddle with Demo

Related Question