Passing column names dynamically to UNPIVOT

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