วิธีสร้าง pivot โดยใช้ sql

Question

ฉันยังใหม่กับ SQL ฉันมีตารางแบบนี้

ด้านล่างนี้คือคำถาม:

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]

และฉันได้รับคำสั่งให้รับข้อมูลแบบนี้

[request_format]

Grandtotalขึ้นอยู่กับจำนวนรวมของbrockrage_amt.

ฉันเข้าใจว่าฉันต้องใช้ฟังก์ชัน PIVOT แต่ไม่สามารถเข้าใจได้อย่างชัดเจน. มันจะช่วยได้มากถ้ามีใครสามารถอธิบายได้ในกรณีข้างต้น (หรือทางเลือกอื่น ๆ ถ้ามี)

Answer 1

ฉันได้ตั้งสมมติฐานว่าผลรวมทั้งหมดเป็นไปตามชื่อประกันภัยและรหัส DO และผลรวมทั้งหมดเป็นผลรวมและไม่นับ นอกจากนี้ยังมีความแตกต่างบางประการระหว่างชื่อเขตข้อมูลในแบบสอบถามของคุณ sql_table และ request_format โค้ดตัวอย่างด้านล่างนี้จะต้องได้รับการปรับให้เข้ากับสถานการณ์เฉพาะของคุณ แต่เป็นโครงสร้างพื้นฐานและรูปแบบที่คุณต้องการ

นอกจากนี้คุณจะไม่ได้รับ request_format ที่แน่นอนเนื่องจากผลลัพธ์ของแบบสอบถามจะไม่มีสีการจัดรูปแบบ ฯลฯ ...

นี่คือตัวอย่างการทำงานพร้อมด้วยข้อมูลตัวอย่าง:

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];

ให้ผลลัพธ์ของ:

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

ไม่มีข้อมูลตัวอย่างดังนั้นฉันเดาว่าการพยายามย้อนยุคให้พอดีกับข้อความค้นหาของคุณจะเป็นดังนี้:

 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];

ฉันคาดเดาว่าจะต้องมีการเปลี่ยนแปลงเนื่องจากไม่มีข้อมูลตัวอย่างและคำจำกัดความของตารางให้ เนื่องจากฉันไม่สามารถเรียกใช้แบบสอบถามอาจมีการพิมพ์ผิด