Come creare pivot usando sql
Sono nuovo in SQL, ho una tabella come questa.
Di seguito è la query:
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]
E mi è stato detto di ottenere dati come questo
[request_format]
Grandtotalsi basa sul conteggio totale di brockrage_amt.
Capisco che devo usare la funzione PIVOT. Ma non riesco a capirlo chiaramente. Sarebbe di grande aiuto se qualcuno potesse spiegarlo nel caso precedente. (O qualsiasi alternativa se ce ne sono)
Risposte
Ho ipotizzato che il totale complessivo si basi sul nome dell'assicurazione e sul codice DO e il totale complessivo è somma e non conta. Esistono anche alcune discrepanze tra i nomi dei campi nella query, sql_table e request_format. Il codice di esempio riportato di seguito dovrà essere adattato alla tua situazione particolare, ma è la struttura e il formato di base che stai chiedendo.
Inoltre, non otterrai l'esatto request_format perché i risultati della query non avranno colore, formattazione, ecc ...
Ecco un esempio funzionante con dati campione inventati:
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];
Fornendoti i risultati di:
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
Non ci sono dati di esempio quindi immagino che tentare di adattare la tua query sarebbe qualcosa del genere:
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];
Suppongo che ci sarà bisogno di modifiche poiché non sono stati forniti dati di esempio e definizioni di tabelle. Poiché non riesco a eseguire la query, potrebbero esserci errori di battitura.