SQL Pivot - 多行多列

Question

我有需要转到 GridView 的数据。它的 2 行连接成一行，然后旋转成列。

下面是数据设置。我只包括了 1 月到 4 月，但这将持续所有月份，年份可能会改变，但始终是连续 2 年。

Sample Data


CREATE TABLE #tmpData (
    [YEAR] INT,
    [AMOUNT] DECIMAL(12,2),
    [PAX]   INT,
    [PRODUCTID] INT,
    [MONTH] INT,
    [MONTHNAME] VARCHAR(10)
)

INSERT INTO #tmpData SELECT 2012    ,3309       ,10 ,1  ,1  ,'January'
INSERT INTO #tmpData SELECT 2013    ,3257.25    ,11 ,1  ,1  ,'January'
INSERT INTO #tmpData SELECT 2012    ,4351.2     ,21 ,1  ,2  ,'February'
INSERT INTO #tmpData SELECT 2013    ,3719.25    ,31 ,1  ,2  ,'February'
INSERT INTO #tmpData SELECT 2012    ,4687       ,11 ,1  ,3  ,'March'
INSERT INTO #tmpData SELECT 2013    ,4120.74    ,11 ,1  ,3  ,'March'
INSERT INTO #tmpData SELECT 2012    ,6123.1     ,21 ,1  ,4  ,'April'
INSERT INTO #tmpData SELECT 2013    ,5417.25    ,21 ,1  ,4  ,'April'

INSERT INTO #tmpData SELECT 2012    ,5416.5     ,10 ,3  ,1  ,'January'
INSERT INTO #tmpData SELECT 2013    ,6104.6     ,20 ,3  ,1  ,'January'
INSERT INTO #tmpData SELECT 2012    ,9748.16    ,30 ,3  ,2  ,'February'
INSERT INTO #tmpData SELECT 2013    ,10797.43   ,30 ,3  ,2  ,'February'
INSERT INTO #tmpData SELECT 2012    ,12706.32   ,30 ,3  ,3  ,'March'
INSERT INTO #tmpData SELECT 2013    ,13194.3    ,4  ,3  ,3  ,'March'
INSERT INTO #tmpData SELECT 2012    ,16429.03   ,33 ,3  ,4  ,'April'
INSERT INTO #tmpData SELECT 2013    ,14339.92   ,37 ,3  ,4  ,'April'

SELECT * FROM   #tmpData
DROP TABLE #tmpData

期望的结果

CREATE TABLE #tmpResults (
    [PRODUCTID]         INT,

    [2013_JAN_AMOUNT]   DECIMAL(12,2),
    [2013_JAN_AMOUNT_%] INT,
    [2013_JAN_PAX]      INT,
    [2013_JAN_PAX_%]    INT,

    [2013_FEB_AMOUNT]   DECIMAL(12,2),
    [2013_FEB_AMOUNT_%] INT,
    [2013_FEB_PAX]      INT,
    [2013_FEB_PAX_%]    INT,

    [2013_MAR_AMOUNT]   DECIMAL(12,2),
    [2013_MAR_AMOUNT_%] INT,
    [2013_MAR_PAX]      INT,
    [2013_MAR_PAX_%]    INT,

    [2013_APR_AMOUNT]   DECIMAL(12,2),
    [2013_APR_AMOUNT_%] INT,
    [2013_APR_PAX]      INT,
    [2013_APR_PAX_%]    INT     
)

INSERT INTO #tmpResults 
    SELECT  1, -- PRODUCTID
        --AMOUNT    CALC= (JAN2013-JAN2012)/JAN2012                         2013PAX CALC= (JAN2013-JAN2012)/JAN2012
         3257.25,   ROUND((SELECT ((3257.25-3309)/3309)*100),0),            11,     CONVERT(INT,ROUND(CONVERT(DECIMAL,11-10)/10*100,0))--JAN2013
        ,3719.25,   ROUND((SELECT ((3719.25-4351.2)/4351.2)*100),0),        31,     CONVERT(INT,ROUND(CONVERT(DECIMAL,31-21)/21*100,0)) --FEB2013
        ,4120.74,   ROUND((SELECT ((4120.74-4687)/4687)*100),0),            11,     CONVERT(INT,ROUND(CONVERT(DECIMAL,11-10)/10*100,0)) --MAR2013
        ,5417.25,   ROUND((SELECT ((5417.25-6123.1)/6123.1)*100),0),        21,     CONVERT(INT,ROUND(CONVERT(DECIMAL,11-10)/10*100,0)) --APR2013

INSERT INTO #tmpResults 
    SELECT  3, -- PRODUCTID
        --AMOUNT    CALC= (JAN2013-JAN2012)/JAN2012                         2013PAX CALC= (JAN2013-JAN2012)/JAN2012
         6104.6,    ROUND((SELECT ((6104.6-5416.5)/5416.5)*100),0),         20,     ROUND((SELECT ((20-10)/10)*100),2)  --JAN2013
        ,10797.43,  ROUND((SELECT ((10797.43-9748.16)/9748.16)*100),0),     30,     ROUND((SELECT ((30-30)/30)*100),2)  --FEB2013
        ,13194.3,   ROUND((SELECT ((13194.3-12706.32)/12706.32)*100),0),    4,      ROUND((SELECT ((4-30)/30)*100),2)   --MAR2013
        ,14339.92,  ROUND((SELECT ((14339.92-16429.03)/16429.03)*100),0),   37,     ROUND((SELECT ((37-33)/33)*100),2)  --APR2013



SELECT * FROM   #tmpResults
DROP TABLE #tmpResults

这是数据@SQLFiddle - http://sqlfiddle.com/#!6/e8ed1/7/1
有什么建议吗？

Answer 1

为了获得结果，您将不得不考虑对表中的数据进行透视和反透视，并且由于您想在任意两年内执行此操作，因此您将不得不考虑应用动态 SQL。

首先，让我们从获取 2 年数据的初始查询开始。你需要在你的表上加入两次才能得到当年和上一年的数据。查询将是:

select t.amount,
  round((t.amount-c.amount)/c.amount*100, 0) AmtPercent,
  t.pax,
  round((t.pax-c.pax)/(c.pax*1.0)*100, 0) PaxPercent,
  t.productid,
  t.monthname,
  t.year
from tmpData t
inner join tmpData c
  on t.monthname = c.monthname
  and t.productid = c.productid
  and c.year = 2012
where t.year = 2013;

参见 SQL Fiddle with Demo .计算完数据后，我建议取消透视 amount、amtpercent、pax 和 paxpercent 列。这会将每个 productid 和 month 的多列变成多行。您最初的 SQL Fiddle 使用的是 SQL Server 2012，因此我假设您使用的是高于 2005 的版本。如果是这样，那么您可以使用带有 VALUES 子句的 CROSS APPLY 来取消数据透视:

select d.productid, 
   cast(year as varchar(4)) + '_' + monthname + '_' + col as col,
   value
from
(
  select t.amount,
    round((t.amount-c.amount)/c.amount*100, 0) AmtPercent,
    t.pax,
    round((t.pax-c.pax)/(c.pax*1.0)*100, 0) PaxPercent,
    t.productid,
    t.monthname,
    t.year
 from tmpData t
 inner join tmpData c
    on t.monthname = c.monthname
    and t.productid = c.productid
    and c.year = 2012
  where t.year = 2013
) d
cross apply
(
  values
    ('Amount', amount),
    ('AmountPercent', amtPercent),
    ('Pax', Pax),
    ('PaxPercent', paxPercent)
) c (col, value);

参见 SQL Fiddle with Demo .这给你一个结果:

| PRODUCTID |                         COL |    VALUE |
------------------------------------------------------
|         1 |         2013_January_Amount |  3257.25 |
|         1 |  2013_January_AmountPercent |       -2 |
|         1 |            2013_January_Pax |       11 |
|         1 |     2013_January_PaxPercent |       10 |
|         1 |        2013_February_Amount |  3719.25 |
|         1 | 2013_February_AmountPercent |      -15 |
|         1 |           2013_February_Pax |       31 |
|         1 |    2013_February_PaxPercent |       48 |
|         1 |           2013_March_Amount |  4120.74 |

现在您的数据是多行，您可以将 PIVOT 函数应用于 col 中的值。这些值是通过将原始列名称与年份和月份连接起来计算得出的。当您应用 PIVOT 函数时，查询将是:

select productid, 
  [2013_January_Amount], [2013_January_AmountPercent],
  [2013_January_Pax], [2013_January_PaxPercent],
  [2013_February_Amount], [2013_February_AmountPercent],
  [2013_February_Pax], [2013_February_PaxPercent],
  [2013_March_Amount], [2013_March_AmountPercent],
  [2013_March_Pax], [2013_March_PaxPercent],
  [2013_April_Amount], [2013_April_AmountPercent],
  [2013_April_Pax], [2013_April_PaxPercent]
from 
(
  select d.productid, 
    cast(year as varchar(4)) + '_' + monthname + '_' + col as col,
    value
  from
  (
    select t.amount,
      round((t.amount-c.amount)/c.amount*100, 0) AmtPercent,
      t.pax,
      round((t.pax-c.pax)/(c.pax*1.0)*100, 0) PaxPercent,
      t.productid,
      t.monthname,
      t.year
    from tmpData t
    inner join tmpData c
      on t.monthname = c.monthname
      and t.productid = c.productid
      and c.year = 2012
    where t.year = 2013
  ) d
  cross apply
  (
    values
      ('Amount', amount),
      ('AmountPercent', amtPercent),
      ('Pax', Pax),
      ('PaxPercent', paxPercent)
  ) c (col, value)
) src
pivot
(
  max(value)
  for col in ([2013_January_Amount], [2013_January_AmountPercent],
              [2013_January_Pax], [2013_January_PaxPercent],
              [2013_February_Amount], [2013_February_AmountPercent],
              [2013_February_Pax], [2013_February_PaxPercent],
              [2013_March_Amount], [2013_March_AmountPercent],
              [2013_March_Pax], [2013_March_PaxPercent],
              [2013_April_Amount], [2013_April_AmountPercent],
              [2013_April_Pax], [2013_April_PaxPercent])
) piv;

参见 SQL Fiddle with Demo .如您所见，如果您要报告所有 12 个月，那么您将对列进行大量硬编码。此外，如果您想更改年份，则必须调整查询。如果您希望它根据您传入的参数进行调整，那么您将需要使用动态 SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @currentYear int = 2013,
    @previousYear int = 2012

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(year as varchar(4)) + '_' + monthname + '_' + col) 
                    from
                    (
                      select year, monthname,
                        DATEPART(MM,monthname+' 01 2013') mth
                      from tmpData
                      where year = @currentYear
                    ) d
                    cross apply
                    (
                      select 'Amount', 1 union all
                      select 'AmountPercent', 2 union all
                      select 'Pax', 3 union all
                      select 'PaxPercent', 4
                    ) c (col, so)
                    group by year, monthname, col, so, mth
                    order by year, mth, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT productid,' + @cols + ' 
            from 
            (
              select d.productid, 
                cast(year as varchar(4)) + ''_'' + monthname + ''_'' + col as col,
                value
              from
              (
                select t.amount,
                  round((t.amount-c.amount)/c.amount*100, 0) AmtPercent,
                  t.pax,
                  round((t.pax-c.pax)/(c.pax*1.0)*100, 0) PaxPercent,
                  t.productid,
                  t.monthname,
                  t.year
                from tmpData t
                inner join tmpData c
                  on t.monthname = c.monthname
                  and t.productid = c.productid
                  and c.year = '+cast(@previousYear as varchar(4))+'
                where t.year = '+cast(@currentYear as varchar(4))+'
              ) d
              cross apply
              (
                values
                  (''Amount'', amount),
                  (''AmountPercent'', amtPercent),
                  (''Pax'', Pax),
                  (''PaxPercent'', paxPercent)
              ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

参见 SQL Fiddle with Demo .这些查询给出了一个结果:

| PRODUCTID | 2013_JANUARY_AMOUNT | 2013_JANUARY_AMOUNTPERCENT | 2013_JANUARY_PAX | 2013_JANUARY_PAXPERCENT | 2013_FEBRUARY_AMOUNT | 2013_FEBRUARY_AMOUNTPERCENT | 2013_FEBRUARY_PAX | 2013_FEBRUARY_PAXPERCENT | 2013_MARCH_AMOUNT | 2013_MARCH_AMOUNTPERCENT | 2013_MARCH_PAX | 2013_MARCH_PAXPERCENT | 2013_APRIL_AMOUNT | 2013_APRIL_AMOUNTPERCENT | 2013_APRIL_PAX | 2013_APRIL_PAXPERCENT |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|         1 |             3257.25 |                         -2 |               11 |                      10 |              3719.25 |                         -15 |                31 |                       48 |           4120.74 |                      -12 |             11 |                     0 |           5417.25 |                      -12 |             21 |                     0 |
|         3 |              6104.6 |                         13 |               20 |                     100 |             10797.43 |                          11 |                30 |                        0 |           13194.3 |                        4 |              4 |                   -87 |          14339.92 |                      -13 |             37 |                    12 |