So erstellen Sie einen Pivot mit SQL
Ich bin neu in SQL, ich habe eine Tabelle wie diese.
Unten ist die Abfrage:
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]
Und mir wurde gesagt, ich solle solche Daten bekommen
[angefordertes_Format]
Grandtotalbasiert auf der Gesamtzahl von brockrage_amt.
Ich verstehe, dass ich die PIVOT-Funktion verwenden muss. Kann es aber nicht klar verstehen. Es wäre eine große Hilfe, wenn jemand dies im obigen Fall erklären könnte (oder gegebenenfalls Alternativen).
Antworten
Ich habe angenommen, dass die Gesamtsumme auf dem Versicherungsnamen und dem DO-Code basiert und die Gesamtsumme die Summe ist und nicht zählt. Es gibt auch einige Diskrepanzen zwischen den Feldnamen in Ihrer Abfrage, der sql_table und dem request_format. Der folgende Beispielcode muss an Ihre spezielle Situation angepasst werden, es handelt sich jedoch um die grundlegende Struktur und das Format, nach denen Sie fragen.
Außerdem erhalten Sie nicht das genaue request_format, da die Abfrageergebnisse keine Farbe, Formatierung usw. haben.
Hier ist ein Arbeitsbeispiel mit zusammengestellten Beispieldaten:
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];
Geben Sie die Ergebnisse von:
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
Da es keine Beispieldaten gibt, würde der Versuch, Ihre Abfrage nachzurüsten, ungefähr so aussehen:
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];
Ich spekuliere, dass es Änderungen geben muss, da keine Beispieldaten und Tabellendefinitionen bereitgestellt wurden. Da ich die Abfrage nicht ausführen kann, kann es zu Tippfehlern kommen.