SQL Server - PIVOT de Multiples Agrégats

Compte tenu de l'ensemble de résultats suivant:

---------------------------------------------------------
CustomerID  Service  TransType  SubTotal   Tax   NetTotal
---------------------------------------------------------
106            A        CREDIT     12.52     -      12.52 
106            A        CREDIT     10.07     -      10.07
106            B        CREDIT      2.00     -       2.00
106            C        REMOTE      5.99     -       5.99
106            C        CREDIT      5.99     -       5.99
106            C        CREDIT      3.99  0.30       3.69
106            C        CREDIT      5.99  0.30       5.69
106            D        CREDIT      5.99     -       5.99
---------------------------------------------------------

Noter que NetTotal = sous-total Impôts

Merci de m'aider à calculer la somme(sous-total), somme(de l'Impôt) et la somme(NetTotal), avec pivotted TransType, comme suit:

--------------------------------------------------------------------------
CustomerID  Service  Cash  Check  Credit  Remote  SubTotal   Tax  NetTotal
--------------------------------------------------------------------------
106            A        0      0   22.59       0     22.59     0     22.59   
106            B        0      0    2.00       0      2.00     0      2.00    
106            C        0      0   15.97    5.99     21.96  0.60     21.36    
106            D        0      0    5.99       0      5.99     0      5.99    
--------------------------------------------------------------------------

Si j'avais seulement 1 colonne à être résumées il serait de droite vers l'avant à l'aide de PIVOT, mais je ne suis pas sûr de savoir comment obtenir les 3 agrégats pour les sous-total, l'Impôt et NetTotal.

Merci pour votre aide!

OriginalL'auteur Gus Cavalcanti | 2009-08-07

  1. 26

    Cela peut être fait sans un PIVOT:

    SELECT 
  CustomerID
, [Service]
, Cash = SUM(case when TransType='CASH' then SubTotal else 0 end)
, [Check] = SUM(case when TransType='CHECK' then SubTotal else 0 end)
, Credit = SUM(case when TransType='CREDIT' then SubTotal else 0 end)
, [Remote] = SUM(case when TransType='REMOTE' then SubTotal else 0 end)
, SubTotal = SUM(SubTotal)
, Tax = SUM(Tax)
, NetTotal = SUM(NetTotal)
FROM YourTable
GROUP BY CustomerId, [Service]

    Avec PIVOT, il devient beaucoup plus complexe. La meilleure façon que je peux penser à est de calculer le sous-total, l'Impôt, et NetTotal dans une autre requête, et puis de combiner les requêtes avec une jointure. L'exemple ci-dessous, afin de conserver la requête simple, je l'ai jeté en Espèces ou par Chèque.

    SELECT  
  a.CustomerId
, a.Service
, Credit = a.Credit
, [Remote] = a.[Remote]
, SubTotal = SUM(b.SubTotal)
, Tax = SUM(b.Tax)
, NetTotal = SUM(b.NetTotal)
FROM (
    SELECT 
      CustomerId
    , [Service]
    , Credit = SUM(Credit)
    , [Remote] = SUM([Remote])
    FROM YourTable a
    PIVOT
    (
        SUM(SubTotal) FOR [TransType] IN ([Credit],[Remote])
    ) pvt
    GROUP BY CustomerId, [Service]
) a
INNER JOIN YourTable b 
    ON a.CustomerID = b.CustomerID 
    AND a.[Service] = b.[Service]
GROUP BY a.CustomerId, a.[Service], a.Credit, a.[Remote]
    Grâce Andomar! J'ai été me limitant à l'aide de PIVOT et a supervisé une solution beaucoup plus simple comme celui que vous m'avez donné. Je vous remercie de votre aide!

    OriginalL'auteur Andomar

  2. 7

    Avez-vous essayé quelque chose comme ça?

    DECLARE @Table TABLE(
CustomerID INT,
[Service] VARCHAR(MAX),
TransType VARCHAR(MAX),
SubTotal FLOAT,
Tax FLOAT,
NetTotal FLOAT
)
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'A', 'CREDIT', 12.52, 0 , 12.52
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'A', 'CREDIT', 10.07, 0 , 10.07
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'B', 'CREDIT', 2, 0 , 2
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'C', 'REMOTE', 5.99, 0 , 5.99
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'C', 'CREDIT', 5.99, 0 , 5.99
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'C', 'CREDIT', 3.99, 0.3 , 3.69
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'C', 'CREDIT', 5.99, 0.3 , 5.69
INSERT INTO @Table (CustomerID,[Service],TransType,SubTotal,Tax,NetTotal)
SELECT 106, 'D', 'CREDIT', 5.99, 0 , 5.99
SELECT  Pivots.CustomerID,
Pivots.[Service],
Pivots.Cash,
Pivots.[Check],
Pivots.Credit,
Pivots.[Remote],
Total.SumSubTotal,
Total.SumTax,
Total.SumNetTotal
FROM    (
SELECT  CustomerID,
[Service],
[Cash] Cash,
[Check] [Check],
[Credit] Credit,
[Remote] [Remote]
FROM    (
SELECT  CustomerID,
[Service],
TransType,
SubTotal
FROM    @Table
) sub
PIVOT
(
SUM(SubTotal)
FOR [TransType] IN ([Cash],[Check],[Credit],[Remote])
) pvt
) Pivots INNER JOIN
(                       
SELECT  CustomerID,
[Service],
SUM(SubTotal) SumSubTotal,
SUM(Tax) SumTax,
SUM(NetTotal) SumNetTotal
FROM    @Table
GROUP BY CustomerID,
[Service]
) Total ON  Pivots.CustomerID = Total.CustomerID
AND Pivots.[Service] = Total.[Service]
    Grâce astander. J'ai marqué l'autre réponse comme correcte, car il a résolu le problème de façon beaucoup plus simple. Je vous remercie de votre réponse très très bien.
    +1 pour la réponse correcte et les inserts 🙂

    OriginalL'auteur Adriaan Stander