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

admin

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

sql

我之前曾发布过一个关键问题，并且已经回答了。但是，这种特殊情况需要日期，因为标题和日期将在每次生成数据透视表时更改（并且未知）。我相信我需要根据一些示例进行动态枢轴/取消枢轴，但是我无法弄清楚语法。

表格如下：

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);

任何/所有帮助将不胜感激。

阅读 192

2021-06-07

共1个答案

admin

您首先需要进入UNPIVOT我们的专栏，然后再使用DENSE_RANK()它为您提供一些帮助。将此结果放入临时表中，以便获得逗号分隔的DESNSE_RANK列列表。然后创建一个UNIONso，使其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)

2021-06-07