2

It is possible to do Unpivot operation, where column names are done via select, without using dynamic SQL ?

I tried this, but it fails.

SELECT *
FROM [dbo].[Report]
UNPIVOT
(
  Value
  FOR WeekName IN 
  (
      SELECT COLUMN_NAME FROM information_schema.columns c
      WHERE c.TABLE_NAME = 'Report'
      AND ORDINAL_POSITION > 1
      ORDER BY ORDINAL_POSITION
  )
) u
3

UNPIVOT is not the best way to unpivot a table, CROSS APPLY with VALUES is a far better method.

Sorry, but I don't think you can get away from Dynamic SQL for this one.

This code will dynamically unpivot an entire table.

DECLARE @tablename VARCHAR(255) = 'Employee'
DECLARE @schemaname  VARCHAR(255) = 'HumanResources'
DECLARE @qrystr NVARCHAR(4000) = ''

;with cte
    AS
    (
    SELECT
        c.column_id
        ,c.name
    FROM
        sys.objects o 
            INNER JOIN sys.columns c 
                ON
                o.object_id = c.object_id
    WHERE
        o.name = @tablename
        AND
        SCHEMA_NAME(o.schema_id) = @schemaname
    )
SELECT
@qrystr = 
'SELECT A.[' + (SELECT name FROM cte WHERE column_id = 1) + '] 
    ,C.Name
    ,C.Val
FROM
    [' + @schemaname + '].[' + @tablename + '] A

    CROSS APPLY (VALUES ' +
    STUFF((SELECT ',' + '(CAST(['+name + '] AS VARCHAR(255)),''' + name + ''')' FROM cte WHERE column_id > 1 ORDER BY column_id FOR XML PATH ('')), 1, 1, '') 
    + ' ) C(Val,Name)'

EXEC (@qrystr)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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