转置表 - 透视 SQL Server 2016

Question

我有一个看起来像这样的表格：源表

CREATE TABLE [Temp].[tblMyleneTest]
(
    [DispOrder] [INT] NULL,
    [ReferencePeriod] [VARCHAR](7) NULL,
    [TypeOfGSUF] [VARCHAR](8) NULL,
    [FactAllocReferencePeriod] [VARCHAR](4) NULL,
    [Variable] [VARCHAR](50) NULL,
    [Value] [INT] NULL 
) ON [PRIMARY]

INSERT INTO [Temp].[tblMyleneTest]
VALUES (1, '201710F', 'Reduced', '2015', 'OperatingEntityNumberCount', '13540688'),
       (2, '201710F', 'Reduced', '2015', 'EnterpriseCodeCount', '6041138'),
       (3, '201710F', 'Reduced', '2015', 'EstablishmentCodeCount', '6554081'),
       (4, '201710F', 'Reduced', '2015', 'LocationCodeCount', '6600130'),
       (5, '201710F', 'Reduced', '2015', 'NAICSNotNullCount', '9943634'),
       (6, '201710F', 'Reduced', '2015', 'ReportingEntityNumbeCount', '2608'),
       (7, '201710F', 'Reduced', '2015', 'LocationCode_NotEverAliveCount', '2339'),
       (8, '201710F', 'Reduced', '2015', 'OperatingEntityNumberCountFF', '3078'),
       (1, '201706F', 'Reduced', '2014', 'OperatingEntityNumberCount', '13557946'),
       (2, '201706F', 'Reduced', '2014', 'EnterpriseCodeCount', '6046857'),
       (3, '201706F', 'Reduced', '2014', 'EstablishmentCodeCount', '6560136'),
       (4, '201706F', 'Reduced', '2014', 'LocationCodeCount', '6606226'),
       (8, '201706F', 'Reduced', '2014', 'OperatingEntityNumberCountFF', '3078')

我希望将ReferencePeriod、FactAllocReferencePeriod和TypeOfGSUF分组。如您所见，同一变量名称有 2 条记录。 ReferencePeriod 201710F 和 201706F 打开。 我希望这些计数并排。 我想要这样的东西：

结果表

我能够转动桌子来提升ReferencePeriod。 但不是相关的FactAllocReferencePeriod/TypeOfGSUF...

SELECT * 
FROM [Temp].[tblMyleneTest]
PIVOT (MAX(value) FOR ReferencePeriod IN ([201710F], [201706F])) p

你能帮我这个吗？

Answer 1

在将行透视为列时引用此答案

SELECT
    [Variable], piv.[201706F], piv.[201710F]
FROM 
(
    select [DispOrder], [Variable], [ReferencePeriod], [Value] from  #tblMyleneTest
) src
PIVOT (
    MAX([Value])
    FOR ReferencePeriod IN ([201710F],[201706F])
) piv

订购方式 [分配顺序]

Answer 2

我会为此使用条件聚合而不是枢轴。语法对我来说不那么迟钝。

select variable
    , R201710f = max(case when ReferencePeriod = '201710F' then Value end)
    , F201706f = max(case when ReferencePeriod = '201706F' then Value end)
from tblMyleneTest
group by variable
order by variable

Answer 3

结果

我正在失去 TypeOfGSUF 和 FactAllocReferencePeriod...

Answer 4

这是我的最后一段代码：

    -- Transpose tblSUFGenericCountsReportYYYYMM to get proper display --
SET @p_SQLCMD  = N' DROP TABLE IF EXISTS ' + @p_SUFReportTable + '' 
               + N' SELECT [DispOrder], [Variable], piv.[' + @p_ReferencePeriod2 + '], piv.[' + @p_ReferencePeriod1 + '] '
               + N' INTO ' + @p_SUFReportTable + ' from ( '
               + N' SELECT [DispOrder], [Variable], [ReferencePeriod], [Value] FROM  Temp.tblSUFGenericCountsReport ) src '
               + N' PIVOT ( MAX([Value]) FOR ReferencePeriod IN ([' + @p_ReferencePeriod1 + '],[' + @p_ReferencePeriod2 + ']) ) piv'
               + N' ORDER BY [DispOrder];'
EXEC sp_executesql @statement = @p_SQLCMD

然后添加缺失值：

-- Insert the TypeOfGSUF and FactorAllocationReferenceYear values to the report --
SET @p_SQLCMD  = N' INSERT INTO '+ @p_SUFReportTable +' (DispOrder, variable, "'+ @p_ReferencePeriod2 +'", "'+ @p_ReferencePeriod1 +'", Flag)'
               + N' VALUES (0 ,''TYPE OF GSUF'', "'+ @p_SUFMonthReduced +'", "'+ @p_SUFMonthReduced +'", Null),'
               + N' (1 ,''T4 RY used for allocation view'', ' + @p_PrevFactAllocReferencePeriod + ', ' + @p_FactAllocReferencePeriod + ', Null) ;'
PRINT @p_SQLCMD
EXEC sp_executesql @statement = @p_SQLCMD