Comment créer un pivot à l'aide de SQL

Question

Je suis nouveau dans SQL, j'ai une table comme celle-ci.

Voici la requête:

select 
    gc.GC_Name, dt.GC_SectorType, dt.ageing,
    sum(cast(dt.[Brokerage Debtors] as numeric)) as Brokerage_Amt,
    dt.divisionalofficename  
from
    [AR].[Fact_Brokerage_Debt] dt 
inner join 
    AUM.DIM_BUSINESS_TYPE BT on BT.Business_Type_WId_PK = dt.BusinessType_WID 
inner join 
    aum.Dim_GroupCompany gc on dt.insurer_Wid = gc.GC_WID
where 
    bt.Business_Type_Wid in (4, 8, 10) 
    and dt.ageing <> '<30' 
    and cast(dt.[Brokerage Debtors] as numeric) > 0 
    and gc.GC_SectorType = 'psu'
group by  
    gc.GC_Name, dt.GC_SectorType, dt.ageing, dt.divisionalofficename 

[sql_table]

Et on m'a dit d'obtenir des données comme ça

[format_demandé]

Grandtotalest basé sur le nombre total de brockrage_amt.

Je comprends que je dois utiliser la fonction PIVOT. Mais je ne peux pas le comprendre clairement. Ce serait très utile si quelqu'un pouvait l'expliquer dans le cas ci-dessus (ou toute alternative le cas échéant)

Answer 1

I've made the assumption that the Grand Total is based on the Insurance Name and DO Code and Grand Total is sum and not count. There are also some discrepancies between the field names in your query, the sql_table and request_format. Sample code below will have to be adjusted to your particular situation, but it is the basic structure and format you're asking for.

Also, you will not get the exact request_format because query results will not have color, formatting, etc...

Here's a working example with made up sample data:

DECLARE @testdata TABLE
    (
        [Insurance_Name] VARCHAR(100)
      , [DO_Code] VARCHAR(100)
      , [ageing] VARCHAR(10)
      , [Brokerage_Amt] INT
    );

INSERT INTO @testdata (
                          [Insurance_Name]
                        , [DO_Code]
                        , [ageing]
                        , [Brokerage_Amt]
                      )
VALUES ( 'Insurance Company 1', '123', '31-60', 100 )
     , ( 'Insurance Company 1', '123', '91-120', 200 )
     , ( 'Insurance Company 1', '123', '>=365', 300 )
     , ( 'Insurance Company 1', '234', '61-90', 300 )
     , ( 'Insurance Company 1', '234', '61-90', 300 )
     , ( 'Insurance Company 1', '234', '121-180', 300 )
     , ( 'Insurance Company 1', '234', '181-364', 200 )
     , ( 'Insurance Company 2', '789', '61-90', 50 )
     , ( 'Insurance Company 2', '789', '121-180', 25 )
     , ( 'Insurance Company 2', '789', '181-364', 9 );

SELECT [pvt].[Insurance_Name]
     , [pvt].[DO_Code]
     , [31-60]
     , [61-90]
     , [91-120]
     , [121-180]
     , [181-364]
     , [>=365]
     , [pvt].[GrandTotal]
FROM   (
           SELECT [Insurance_Name]
                , [DO_Code]
                , [ageing]
                , [Brokerage_Amt]
                , SUM([Brokerage_Amt]) OVER ( PARTITION BY [Insurance_Name]
                                                         , [DO_Code]
                                            ) AS [GrandTotal] --here we determine that grand total based on the Insurance_Name and DO_Code
           FROM   @testdata
       ) AS [ins]
PIVOT (
          SUM([Brokerage_Amt]) --aggregate and pivot this column
          FOR [ageing] --sum the above and make column where the value is one of these [31-60], [61-60], etc...
          IN ( [31-60], [61-90], [91-120], [121-180], [181-364], [>=365] )
      ) AS [pvt];

Giving you the results of:

Insurance_Name            DO_Code    31-60       61-90       91-120      121-180     181-364     >=365       GrandTotal
------------------------  ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Insurance Company 1       123        100         NULL        200         NULL        NULL        300         600
Insurance Company 1       234        NULL        600         NULL        300         200         NULL        1100
Insurance Company 2       789        NULL        50          NULL        25          9           NULL        84

There is no sample data so I guess attempting to retro fit your query would be something like this:

 SELECT [pvt].[Insurance_Name]
     , [pvt].[DO_Code]
     , [31-60]
     , [61-90]
     , [91-120]
     , [121-180]
     , [181-364]
     , [>=365]
     , [pvt].[GrandTotal]
FROM   (
           SELECT     [gc].[GC_Name] AS [Insurance_Name]
                    , [dt].[GC_SectorType] AS [DO_Code]
                    , [dt].[ageing]
                    --, SUM(CAST([dt].[Brokerage Debtors] AS NUMERIC)) AS [Brokerage_Amt]
                    , CAST([dt].[Brokerage Debtors] AS NUMERIC) AS [Brokerage_Amt]
                    , SUM(CAST([dt].[Brokerage Debtors] AS NUMERIC)) OVER (PARTITION BY [gc].[GC_Name], [dt].[GC_SectorType]) AS GrandTotal
                    , [dt].[divisionalofficename]
           FROM       [AR].[Fact_Brokerage_Debt] [dt]
           INNER JOIN [AUM].[DIM_BUSINESS_TYPE] [BT]
               ON [BT].[Business_Type_WId_PK] = [dt].[BusinessType_WID]
           INNER JOIN [aum].[Dim_GroupCompany] [gc]
               ON [dt].[insurer_Wid] = [gc].[GC_WID]
           WHERE      [BT].[Business_Type_Wid] IN ( 4, 8, 10 )
                      AND [dt].[ageing] <> '<30'
                      AND CAST([dt].[Brokerage Debtors] AS NUMERIC) > 0
                      AND [gc].[GC_SectorType] = 'psu'
       --I guess you would not need the sum and group by, sum should be hanlded in the pivot, but above we add a sum partioning by [gc].[GC_Name], [dt].[GC_SectorType] for the grand total
       --GROUP BY   [gc].[GC_Name]
       --         , [dt].[GC_SectorType]
       --         , [dt].[ageing]
       --         , [dt].[divisionalofficename];
       ) AS [ins]
PIVOT (
          SUM([Brokerage_Amt]) --aggregate and pivot this column
          FOR [ageing] --sum the above and make column where the value is one of these [31-60], [61-60], etc...
          IN ( [31-60], [61-90], [91-120], [121-180], [181-364], [>=365] )
      ) AS [pvt];

I speculate that there will need to be changes since there was no sample data and table definitions provided. Since I can not run the query there could be typos.