SQL Server 2008 R2 - 带有(移动)日期的动态数据透视/逆透视

Question

我之前发布了一个关键问题，并得到了回答。但是，这种特殊情况需要日期作为标题，并且每次生成数据透视表时日期都会更改(并且是未知的)。我相信我需要基于一些示例的动态枢轴/反枢轴，但我无法弄清楚语法。

这是表:

CREATE 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]

我不需要输出中的任何“关键”列。因此，这里有一个简单的选择表:

select [DAY],[Date],[Bull Bear Gap],[Monthly],[Weekly],[Daily],[Hour 1],[Hour 2],[Hour 3],[Hour 4],[Hour 5],[Hour 6],[Hour 7] 
from PhaseFlowChart
order by pfckey asc

这是上面的输出:



我希望输出(动态枢轴/反枢轴？)看起来像这样(同样，每次生成时日期都会不同，这是每天):



最后，我在网上找到了这个 sql，但经过几天的语法操作后，我无法弄清楚如何移植它以满足我的需求。

CREATE TABLE #yt
    ([ID] int, [expense] int, [revenue] int, [date] datetime)
;

INSERT INTO #yt
    ([ID], [expense], [revenue], [date])
VALUES
    (1, 43, 45, '2012-12-31 00:00:00'),
    (2, 32, 32, '2013-01-01 00:00:00'),
    (3, 64, 56, '2013-01-31 00:00:00'),
    (4, 31, 32, '2013-03-03 00:00:00')

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+'_'+convert(varchar(10), #yt.date, 110)) 
                    from #yt
                    cross apply
                    (
                      select 'expense' col union all
                      select 'revenue'
                    ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' 
            from
            (
              select id, 
                col+''_''+convert(varchar(10), date, 110) new_col, 
                value
              from #yt
              unpivot
              (
                value
                for col in (expense, revenue)
              ) un
            ) src
            pivot 
            (
                sum(value)
                for new_col in (' + @cols + ')
            ) p '

execute(@query);

非常感谢任何/所有帮助。

Answer 1

您首先需要UNPIVOT我们的专栏和使用 DENSE_RANK()给你一些东西以供日后引用。将此结果放入临时表中，以便您可以获得DESNSE_RANK 的逗号分隔列表。列。然后创建一个 UNION以便 Day和 Date与未旋转的列属于同一列。这可以放在一个全局临时表中，以便在动态 SQL 中使用。创建一个变量，用于存储列列表并构建动态 SQL 并执行它。

一个完整的例子(显然不要删除 PhaseFlowChart 表)

-- pre-cleanup
IF OBJECT_ID('[dbo].[PhaseFlowChart]') IS NOT NULL
    DROP TABLE [dbo].[PhaseFlowChart]
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp
GO

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL
    DROP TABLE ##tmp
GO

-- setup table and data
CREATE 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 #tmp
FROM PhaseFlowChart pfc
UNPIVOT (
    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 later
SELECT *
INTO ##tmp
FROM (
      -- 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 PIVOT
DECLARE @cols VARCHAR(MAX) = 
STUFF(CAST((SELECT ',' + QUOTENAME(dr)
       FROM (
           SELECT DISTINCT dr
           FROM #tmp
       ) t
       ORDER BY dr
       FOR XML PATH(''), TYPE
      ) AS VARCHAR(MAX)),1,1,'')

-- create and execute the sql
DECLARE @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)