How to create pivot using sql

Question

I am new to SQL, I have a table like this.

Below is the 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]

And I was told to get data like this

[requested_format]

Grandtotal is based on total count of brockrage_amt.

I understand I need to use PIVOT function. But can't understand it clearly. It would be great help if somebody can explain it in above case.(or any alternatives if any)

have you read this ? https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15#see-also plus alot of examples if you google — eshirvana, Oct 30 '20 at 16:27
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Luuk, Oct 30 '20 at 16:32
You can find some SQL Pivot queries here http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx — Eralper, Oct 30 '20 at 18:16
You have already asked this question twice before here https://stackoverflow.com/questions/64579079/need-to-customized-my-query-as-per-below-format and another asking about the same format and SSIS https://stackoverflow.com/questions/64585417/how-we-can-send-email-through-ssis-by-attaching-the-data-in-excel-to-per-person. The sample table data does not match or with your requested format. — Tim Mylott, Oct 30 '20 at 19:59
you say the grand total is based on total count of brockage_amt. Total count? or sum? and is that based on just insurance name? Or insurance name and do code? meaning if there was another do code for the same insurance name it would be second line item with a different grand total? — Tim Mylott, Oct 30 '20 at 20:11
I answered your question here, you don't accept my answer, then you changed your question to something different then what you originally asked and then I see you used what I provided here in another post asking the same question you've already posted multiple times. That's not how you get help on this site. — Tim Mylott, Nov 04 '20 at 02:16
Hello nice to see your comments on my post ..u r seriously such good human being who help me ..and yes the only reason behind changing this question i used some data of my office by mistake ..i got worried due to which i have deleted all ..i visited your dashboard to connect to you personally for thanking you but won't able...thanks a lot ..for all your help — Umeshdan, Nov 04 '20 at 12:19
Also I'm still learning this dashboard. And new into the technology that's the reason getting multiple question based on scenario where I could required your help..so please don't take anything wrong being sorry if I did wrong something — Umeshdan, Nov 04 '20 at 12:22
Editing Questions to improve them (e.g. clarification, adding additional information, etc.) *is encouraged*. However, editing a Question to change it into a different question, which invalidates one or more answers, is against policy on Stack Overflow. Your edit here did so. The policy is that other users with edit privileges should proactively revert such changes. I have reverted your edit. You *are encouraged to [ask a new Question](/questions/ask)*, perhaps with a link to this one for additional context. We want to help, but your new/additional issue needs to be a new Question. — Makyen, Nov 05 '20 at 05:24

score 1 · Answer 1 · edited Nov 24 '20 at 16:20

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.

How to create pivot using sql

1 Answers1