Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.