pivot on 2 fields

  • I'm trying to create a query that pivots on 2 fields(entity,channel) and produce a daily sum of charges per day.

    This gives me a daily total( I would like to remove any Null values from the display) for the month, but I want to break it

    down by channel within the entity,

    Data:

    src,200,02,01,400.00

    xrb,200,02,01,500.00

    src,300,02,01,400.00

    xrb,300,02,01,500.00

    desired output

    entity

    01                    02

    200

    src                              400                      0

    xrb                              500                     0

    300

    src                                    0             400

    xrb                                   0             500

    Thanks.

    CREATE TABLE #MonthlyChrgs (
    tmp_channel nvarchar(10),
    tmp_entity nvarchar(10),
    tmp_month nvarchar(10),
    tmp_day nvarchar(10),
    tmp_charges dec(18,2)



    DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)

    select @cols = STUFF((SELECT ',' + QUOTENAME(tmp_day)
    from #MonthlyChrgs
    group by tmp_day
    order by tmp_day
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    set @query = 'SELECT tmp_entity,' + @cols + ' from
    (
    select tmp_entity,tmp_channel, tmp_day, tmp_Charges
    from #MonthlyChrgs
    ) x
    pivot
    (
    sum( tmp_Charges)
    for tmp_day in (' + @cols + ')
    ) p '

    execute(@query);
  • An alternative to PIVOT is conditional aggregation.  The sample data provided only contains 1 value for 'tmp_day' which seems to not agree with the output.  So I changed the sample data so that 'tmp_day' contains both '01' and '02' to match with the output.  Also, it's not clear if it's necessary for this to be dynamic SQL.  The column 'tmp_day' is nvarchar(10) which might suggest there are a fixed number of values.  Maybe you're looking for something like this

    drop table if exists #MonthlyChrgs;
    go
    CREATE TABLE #MonthlyChrgs (
    tmp_channel nvarchar(10),
    tmp_entity nvarchar(10),
    tmp_month nvarchar(10),
    tmp_day nvarchar(10),
    tmp_charges dec(18,2));

    insert into #MonthlyChrgs(tmp_channel, tmp_entity, tmp_month, tmp_day, tmp_charges) values
    ('src','200','02','01',400.00),
    ('xrb','200','02','01',500.00),
    ('src','300','02','02',400.00),
    ('xrb','300','02','02',500.00);

    select tmp_channel, tmp_entity,
    sum(case when tmp_day='01' then tmp_charges else 0 end) day1,
    sum(case when tmp_day='02' then tmp_charges else 0 end) day2,
    sum(case when tmp_day='03' then tmp_charges else 0 end) day3,
    sum(case when tmp_day='04' then tmp_charges else 0 end) day4,
    sum(case when tmp_day='05' then tmp_charges else 0 end) day5,
    sum(case when tmp_day='06' then tmp_charges else 0 end) day6,
    sum(case when tmp_day='07' then tmp_charges else 0 end) day7
    from #MonthlyChrgs
    group by tmp_channel, tmp_entity
    order by tmp_entity, tmp_channel;
    tmp_channel	tmp_entity	day1	day2	day3	day4	day5	day6	day7
    src 200 400.00 0.00 0.00 0.00 0.00 0.00 0.00
    xrb 200 500.00 0.00 0.00 0.00 0.00 0.00 0.00
    src 300 0.00 400.00 0.00 0.00 0.00 0.00 0.00
    xrb 300 0.00 500.00 0.00 0.00 0.00 0.00 0.00

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I was trying to get the days so they would be dynamic rather than having to hard those each month.. I also wanted

    the channel to be sub-category of the entity..

     

    Thanks.

  • Based on the data that's been provided the two tmp_day values are '01' and '02' which don't seem to vary by month.  As to which column is subordinate to the other, it could be switched in the GROUP BY/ORDER BY clause(s) to whatever is appropriate

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Bruin wrote:

    I was trying to get the days so they would be dynamic rather than having to hard those each month.. I also wanted the channel to be sub-category of the entity..

    Thanks.

    The original name for such "conditional aggregations" is CROSSTABs.  To do them fairly easily using CROSSTABs, please see the following article.

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

    They were a life saver for me in the past.

    There's also a first article on the subject which compares the performance (on older machines) of CROSSTABs vs PIVOTS.  In most cases, CROSSTABs (especially with "pre-aggregation" if it's needed) pan out to be quite a bit faster than PIVOTs.

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I would like to be able to run this for a given Year, is that possible using crosstabs?

    Thanks again

  • Bruin wrote:

    I would like to be able to run this for a given Year, is that possible using crosstabs?

    Thanks again

    How are you going to present a row with 365/366 columns to a client?  And how many years would be included - 3 years, 10 years - 100 years?

    I would rollup the numbers to either a weekly (52/53 weeks) or monthly (12 month) cross-tab (pivot).  For reporting you can then drill-down or drill-through to expand for a given time frame.  For example, user selects the totals column for month 10 last year and opens a report that displays that year and months detail data.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I want to show current year and previous.. do you have any examples based upon my current query?

    Thanks again...

    • This reply was modified 1 month, 1 week ago by  Bruin.
  • Bruin wrote:

    I want to show current year and previous.. do you have any examples based upon my current query?

    Thanks again...

    I'm pretty confused about what you're actually using for a source table and what you want the output to look like.  Take a look at the first link in my signature line below for one way to post sample data, please.  A cleaner description of what you want for the output would be helpful, as well.

    And none of this is difficult... we just need to know the right "gazintas" and "gozotas".  😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Data:

    src,200,2021,01,01,400.00

    xrb,200,2021,01,01,500.00

    src,300,2021,02,01,400.00

    xrb,300,2021,02,01,500.00

     

    I would like to rollup the data at a monthly level by year entity and channel. If no data reported for the month than 0 displayed

    Jan                Feb             Mar    May  ...

    Year  2021

    entity   200

    channel   src                                 400                   0

    xrb                                 500                   0

    entity   300

    src                                        0                400

    xrb                                       0                 500

    I'm trying to get a breakdown by Year,Entity and channel for charges.

    Thanks.

     

    CREATE TABLE #MonthlyChrgs (
    tmp_channel nvarchar(10),
    tmp_entity nvarchar(10),
    tmp_year nvarchar(4),
    tmp_month nvarchar(10),
    tmp_day nvarchar(10),
    tmp_charges dec(18,2)
  • It would have been much more neighborly of you if you had read the article at the link I directed you to and provided the data in a "readily consumable" format.  If you want help in the future, consider doing such a thing on future posts.  😀

    Here's  the code with the data you provided in a readily consumable format (one of many methods).

       DROP TABLE IF EXISTS #MonthlyChrgs;
    CREATE TABLE #MonthlyChrgs
    (
    tmp_channel NVARCHAR(10)
    ,tmp_entity NVARCHAR(10)
    ,tmp_year NVARCHAR(4)
    ,tmp_month NVARCHAR(10)
    ,tmp_day NVARCHAR(10)
    ,tmp_charges DEC(18,2)
    )
    ;
    INSERT INTO #MonthlyChrgs
    (tmp_channel, tmp_entity, tmp_year, tmp_month, tmp_day, tmp_charges)
    VALUES --Data for 2021
    ('src','200','2021','01','01',400.00)
    ,('xrb','200','2021','01','01',500.00)
    ,('src','300','2021','02','01',400.00)
    ,('xrb','300','2021','02','01',500.00)
    --Data for 2020
    ,('src','200','2020','01','01',400.00)
    ,('xrb','200','2020','01','01',500.00)
    ,('src','300','2020','02','01',400.00)
    ,('xrb','300','2020','02','01',500.00)
    ;

    As a bit of a sidebar, consider NOT using NVARCHAR() for numeric data.

    Here's the "simple" code to do only what you ask.  If you need subtotals, line totals, etc, etc, let us know.

    DECLARE @Year INT = 2021
    ;
    --===== Solve the problem using a CROSSTAB, like what is in the article links I posted.
    SELECT tmp_year
    ,tmp_channel
    ,tmp_entity
    ,[Jan] = SUM(CASE WHEN tmp_month = '01' THEN tmp_charges ELSE 0 END)
    ,[Feb] = SUM(CASE WHEN tmp_month = '02' THEN tmp_charges ELSE 0 END)
    ,[Mar] = SUM(CASE WHEN tmp_month = '03' THEN tmp_charges ELSE 0 END)
    ,[Apr] = SUM(CASE WHEN tmp_month = '04' THEN tmp_charges ELSE 0 END)
    ,[May] = SUM(CASE WHEN tmp_month = '05' THEN tmp_charges ELSE 0 END)
    ,[Jun] = SUM(CASE WHEN tmp_month = '06' THEN tmp_charges ELSE 0 END)
    ,[Jul] = SUM(CASE WHEN tmp_month = '07' THEN tmp_charges ELSE 0 END)
    ,[Aug] = SUM(CASE WHEN tmp_month = '08' THEN tmp_charges ELSE 0 END)
    ,[Sep] = SUM(CASE WHEN tmp_month = '09' THEN tmp_charges ELSE 0 END)
    ,[Oct] = SUM(CASE WHEN tmp_month = '10' THEN tmp_charges ELSE 0 END)
    ,[Nov] = SUM(CASE WHEN tmp_month = '11' THEN tmp_charges ELSE 0 END)
    ,[Dec] = SUM(CASE WHEN tmp_month = '12' THEN tmp_charges ELSE 0 END)
    ,[LineTotal] = SUM(tmp_charges)
    FROM #MonthlyChrgs
    WHERE tmp_year IN (@Year, @Year-1)
    GROUP BY tmp_year, tmp_channel, tmp_entity
    ORDER BY tmp_year, tmp_channel, tmp_entity
    ;

    Here are the results...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Cool Thanks again!!!!

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply