主页 / dba / 问题 / 158461
Accepted
Muflix
Asked: 2016-12-18 10:06:40 +0800 CST

基于选择的逆透视列

  • 772

可以执行 Unpivot 操作，其中列名通过 select 完成，而不使用动态 SQL？

我试过了，但失败了。

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
sql-server-2008 unpivot

1 个回答

  1. Best Answer

    UNPIVOT 不是反透视表的最佳方法，CROSS APPLY with VALUES 是更好的方法。

    抱歉，但我认为您无法为此摆脱动态 SQL。

    此代码将动态取消透视整个表。

    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)
    • 3

相关问题