基于“一列的SUM除以另一列的SUM"的SQL Pivot查询. [英] SQL Pivot query based on "SUM of a column divided by SUM of another column"

问题描述

我正在尝试找出如何根据特定列的SUM除以另一列的SUM来轮换数据."到目前为止，这是我到达的位置，但是PIVOT查询给我错误: /p>

I am trying to figure out how to pivot my data based on the SUM of a particular column divided by the SUM of another Column". Here is where I've reached so far but the PIVOT query is giving me errors:

USE MyDatabase

SELECT  [PropertyCode],
        [Market],
        [JANUARY 2015],
        [FEBRUARY 2015],
        [MARCH 2015]

FROM (SELECT [PropertyCode],
             [Market],  
             [MTH],
             [GN],
             [PkgRev]


     FROM   HOLDINGS

     ) a

 PIVOT (Sum([PkgRev])/Sum([GN])
         FOR [MTH] IN ([JANUARY 2015],
                   [FEBRUARY 2015],
                   [MARCH 2015])) as pvttable

ORDER BY [PROPERTYCODE]

上面查询中的

HOLDINGS是一个VIEW表，而PkgRev和GN是该VIEW表中的列.在执行PkgRev列的SUM除以GN列的SUM之后，我需要数据透视查询返回值.

HOLDINGS in the query above is a VIEW table and PkgRev and GN are columns in that VIEW table. I need my Pivot query to return values after performing a SUM of the PkgRev column DIVIDED by the SUM of the GN column.

以下是数据的示例(来自HOLDINGS视图表):

Here is a sample of the data (from the HOLDINGS view table):

PropertyCode    ID  GN  MTH    Pkg Rev  Market
   A            1   10  Jan-15  95,652  United Kingdom
   A            3   20  Jan-15  95,652  United Kingdom
   A            12  24  Feb-15  81,610  France
   A            15  14  Feb-15  47,606  France
   B            16  18  Jan-15  38,153  France
   B            20  28  Feb-15  69,391  United Kingdom
   B            22  32  Jan-15  107,990 Germany
   B            25  28  Feb-15  76,696  United Kingdom
   C            26  4   Jan-15  18,961  Germany
   C            27  26  Jan-15  56,522  South Africa
   C            29  20  Feb-15  27,826  India
   C            31  18  Jan-15  33,661  France
   C            32  14  Jan-15  26,039  Germany

预期输出如下:

PropertyCode    Market           Jan-15     Feb-15
     A          France             0         3,400
     A          United Kingdom     6,377       0
     B          France             2,120       0
     B          Germany            3,375       0
     B          United Kingdom     0         2,609
     C          France             1,870       0
     C          Germany            1,607       0
     C          India              0         1,391
     C          South Africa       2,174       0

推荐答案

旋转时，您应该记住: 1:aggregation and spreading elements cannot directly be results of expressions 2:分组元素是identified by elimination

When pivoting you should remember that: 1: aggregation and spreading elements cannot directly be results of expressions 2: grouping element are identified by elimination

我建议您重写如下:

DECLARE @t TABLE
    (
      ProperTyCode CHAR(1) ,
      ID INT ,
      GN int ,
      MTH NVARCHAR(10) ,
      Pkg int ,
      Market NVARCHAR(20)
    )

INSERT  INTO @t
VALUES  ( 'A', 1, 10, 'Jan-15', 95652, 'United Kingdom' ),
        ( 'A', 3, 20, 'Jan-15', 95652, 'United Kingdom' ),
        ( 'A', 12, 24, 'Feb-15', 81610, 'France' ),
        ( 'A', 15, 14, 'Feb-15', 47606, 'France' ),
        ( 'B', 16, 18, 'Jan-15', 38153, 'France' ),
        ( 'B', 20, 28, 'Feb-15', 69391, 'United Kingdom' ),
        ( 'B', 22, 32, 'Jan-15', 107990, 'Germany' ),
        ( 'B', 25, 28, 'Feb-15', 76696, 'United Kingdom' ),
        ( 'C', 26, 4, 'Jan-15', 18961, 'Germany' ),
        ( 'C', 27, 26, 'Jan-15', 56522, 'South Africa' ),
        ( 'C', 29, 20, 'Feb-15', 27826, 'India' ),
        ( 'C', 31, 18, 'Jan-15', 33661, 'France' ),
        ( 'C', 32, 14, 'Jan-15', 26039, 'Germany' );
WITH    cte
          AS ( SELECT   [PropertyCode] ,
                        [Market] ,
                        [MTH] ,
                        CAST(ROUND(SUM([Pkg])  / CAST(SUM([GN]) AS DECIMAL(20, 5)), 0) AS INT) AS [GR]
               FROM     @t
               GROUP BY [PropertyCode] ,
                        [Market] ,
                        [MTH]
             )
    SELECT  [PropertyCode] ,
            [Market] ,
            ISNULL([Jan-15], 0) AS [Jan-15],
            ISNULL([Feb-15], 0) AS [Feb-15]
    FROM    cte PIVOT ( MAX([GR]) FOR [MTH] IN ( [Jan-15], [Feb-15] ) ) AS pvttable
    ORDER BY [PROPERTYCODE]

输出:

PropertyCode    Market          Jan-15  Feb-15
A               France          0       3400
A               United Kingdom  6377    0
B               France          2120    0
B               Germany         3375    0
B               United Kingdom  0       2609
C               France          1870    0
C               Germany         2500    0
C               India           0       1391
C               South Africa    2174    0    

这篇关于基于“一列的SUM除以另一列的SUM"的SQL Pivot查询.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！