SQL Server 2008 R2-具有（移动）日期的动态数据透视取消透视

SQL Server 2008 R2-具有（移动）日期的动态数据透视/取消透视

您首先需要进入

UNPIVOT

我们的专栏，然后再使用

DENSE_RANK()

它为您提供一些帮助。将此结果放入临时表中，以便获得逗号分隔的

DESNSE_RANK

列列表。然后创建一个

UNIOn

so，使其

Day

与

Date

未透视图属于同一列。可以将其放在全局临时表中以用于动态SQL。创建一个变量，该变量将存储列列表，并构建动态SQL并执行它。

一个完整的例子（显然不要丢

PhaseFlowChart

表）

-- pre-cleanupIF OBJECT_ID('[dbo].[PhaseFlowChart]') IS NOT NULL    DROp TABLE [dbo].[PhaseFlowChart]GOIF OBJECT_ID('tempdb..#tmp') IS NOT NULL    DROP TABLE #tmpGOIF OBJECT_ID('tempdb..##tmp') IS NOT NULL    DROP TABLE ##tmpGO-- setup table and dataCREATE TABLE [dbo].[PhaseFlowChart](    [pfckey] [int] NULL,    [hourlykey] [bigint] NULL,    [daykey] [bigint] NULL,    [weekkey] [int] NULL,    [monthkey] [int] NULL,    [bbkey] [int] NULL,    [Day] [varchar](100) NULL,    [Date] [varchar](100) NULL,    [Bull Bear Gap] [varchar](100) NULL,    [Monthly] [varchar](100) NULL,    [Weekly] [varchar](100) NULL,    [Daily] [varchar](100) NULL,    [Hour 1] [varchar](100) NULL,    [Hour 2] [varchar](100) NULL,    [Hour 3] [varchar](100) NULL,    [Hour 4] [varchar](100) NULL,    [Hour 5] [varchar](100) NULL,    [Hour 6] [varchar](100) NULL,    [Hour 7] [varchar](100) NULL) ON [PRIMARY]INSERT INTO [dbo].PhaseFlowChart     ([Day], [Date], [Bull Bear Gap], Monthly, Weekly, Daily,         [Hour 1], [Hour 2], [Hour 3], [Hour 4], [Hour 5], [Hour 6], [Hour 7])VALUES('MON', '20130101', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1', 'P1'),('TUE', '20130102', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2', 'P2'),('WED', '20130103', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3', 'P3'),('THU', '20130104', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4', 'P4'),('FRI', '20130105', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5', 'P5'),('SAT', '20130106', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6', 'P6'),('SUN', '20130107', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7', 'P7')GO-- unpivot the columns into 'categories'SELECT [Day], [Date], [Value], [Category],     DENSE_RANK() OVER (ORDER BY CAST([Date] AS DATE)) dr INTO #tmpFROM PhaseFlowChart pfcUNPIVOT (    Value FOR Category IN ([Bull Bear Gap], Monthly, Weekly, Daily,       [Hour 1], [Hour 2], [Hour 3], [Hour 4],[Hour 5], [Hour 6], [Hour 7])) upiv-- create a global temp table for use laterSELECt *INTO ##tmpFROM (      -- union data into single category column    SELECt 'Day' Category, [Day] Value, dr, 1 o FROM #tmp    UNIOn ALL    SELECt 'Date' Category, [Date] Value, dr, 2 o FROM #tmp    UNIOn ALL    SELECt [Category], Value, dr, 3 o FROM #tmp) t-- get a comma seperated list of columns for the PIVOTDECLARE @cols VARCHAr(MAX) = STUFF(CAST((SELECt ',' + QUOTENAME(dr)       FROM (SELECt DISTINCT drFROM #tmp       ) t       ORDER BY dr       FOR XML PATH(''), TYPE      ) AS VARCHAr(MAX)),1,1,'')-- create and execute the sqlDECLARE @sql VARCHAr(MAX) = '    SELECt Category, ' + @cols + '    FROM ##tmp    PIVOT (        MAX([Value])        FOR dr IN (' + @cols + ')    ) piv         ORDER BY o, CASE Category          WHEN ''Daily'' THEN 4         WHEN ''Weekly'' THEN 3         WHEN ''Monthly'' THEN 2         WHEN ''Bull Bear Gap'' THEN 1         ELSE 5 END, Category'EXEC(@sql)