基于字段而不是硬编码枢轴字段旋转数据

Question

ItemKey UpdatedOnHandQty    AffectedDate
20406   1594.03     2013-12-27 00:00:00.000
20406   842.132     2014-01-09 00:00:00.000
20406   539.03     2014-02-11 00:00:00.000
20406   486.183    2014-05-12 00:00:00.000
20406   917.86     2014-06-27 00:00:00.000
20406   1314.209   2014-07-29 00:00:00.000
20406   512.261    2014-10-20 00:00:00.000
20406   1394.261    2014-11-12 00:00:00.000
20406   640.19     2014-12-30 00:00:00.000
20406   819.992    2014-08-11 00:00:00.000

我想在SQL中旋转此数据，而不必硬编码枢轴字段名称必须是什么。我只希望每个唯一的日期(年，月，一天(都有一个字段名称。是否有一种基于此领域的枢轴的方法，因为我有100个独特的情感日期。最后，这将在BI工具中过滤，但是对于本报告，我需要枢转日期字段，而Tableau不允许在Excel之外使用枢轴。

Answer 1

不幸的是，使用SQL Server，您无法为动态PIVOT's或任何动态SQL创建VIEW。

存储过程可以为您提供能够执行此操作。

代码：(演示(

DECLARE @sql NVARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @isNullColumns VARCHAR(MAX)
SET @sql = N''
SET @columns = ''
SET @isNullColumns = ''
; WITH item_dates AS
(
  SELECT DISTINCT CONVERT(VARCHAR(10), AffectedDate, 112) AS AffectedDate
  FROM table1
)
SELECT @columns += N',' + QUOTENAME(AffectedDate)
  , @isNullColumns += N',ISNULL(' + QUOTENAME(AffectedDate) + ', 0) AS ' +  QUOTENAME(AffectedDate)
FROM item_dates
ORDER BY AffectedDate
SET @columns = STUFF(@columns, 1, 1, '')
SET @isNullColumns = STUFF(@isNullColumns, 1, 1, '')
SET @sql = N'
;WITH cte AS
(
  SELECT ItemKey
    , CONVERT(VARCHAR(10), AffectedDate, 112) AS AffectedDate
    , UpdatedOnHandQty
  FROM table1
)
SELECT ItemKey
  , ' + @isNullColumns + '
FROM cte
PIVOT 
(
  SUM(UpdatedOnHandQty) FOR AffectedDate IN (' + @columns + ')
) as p'
EXEC sp_executesql @sql;

说明：

• 为了动态地执行此操作，您需要将动态SQL语句存储在变量(例如：@sql(中。
• 列的名称存储在变量(@columns(中，以根据AffectedDate列的DISTINCT值动态构建列。
• @isNullColumns变量用于获取包裹在ISNULL()函数上的列名。否则，您的枢轴数据可能最终会在您的输出中以NULL's的形式出现。