Forum Stats

  • 3,855,327 Users
  • 2,264,499 Discussions
  • 7,905,970 Comments

Discussions

Pivot format

User_5F8TP
User_5F8TP Member Posts: 90 Blue Ribbon
edited Jan 4, 2019 5:46PM in SQL & PL/SQL

How can I put following two columns in pivot form. Thanks in advance!

create table claim

(posted_date date,

claim              number

)

posted datesum_claim
2015-10582
2015-11347
2015-12458
2015-7456
2015-8461
2015-9492
2016-1376
2016-10446
2016-11329
2016-12434
2016-2396
2016-3460
2016-4551
2016-5401
2016-6376
2016-7454
2016-8476
2016-9498
2017-1408
2017-10435
2017-11510
2017-12462
2017-2388
2017-3484
2017-4408
2017-5404
2017-6569
2017-7398
2017-8490
2017-9605
2018-1389
2018-2378
2018-3541
2018-4392
2018-5364
2018-6

443

Tagged:
User_5F8TPmathguyjaramill
«13

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Sep 18, 2018 2:32PM

    Hi,

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
    Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

    Always say which version of Oracle you're using (for example, 12.2.0.1.0).
    See the forum FAQ:

    The Forum FAQ also has a page about pivoting:

    841137 wrote:How can I put following two columns in pivot form. Thanks in advance! create table claim(posted_date date,claim number)posted datesum_claim2015-105822015-113472015-124582015-74562015-84612015-94922016-13762016-104462016-113292016-124342016-23962016-34602016-45512016-54012016-63762016-74542016-84762016-94982017-14082017-104352017-115102017-124622017-23882017-34842017-44082017-54042017-65692017-73982017-84902017-96052018-13892018-23782018-35412018-43922018-53642018-6443

    Is that the output you want?

    That looks like a job for GROUP BY, not pivot.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 18, 2018 2:36PM
    Frank Kulash wrote:Is that the output you want?That looks like a job for GROUP BY, not pivot.

    Frank I think that is the DATA he's given (which he didn't put as DML statements).

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Sep 18, 2018 2:39PM
    SQL> with claim (posted_date,sum_claim) as (select '2015-10',    582 from dual union allselect '2015-11',    347 from dual union allselect '2015-12',    458 from dual union allselect '2015-7',    456 from dual union allselect '2015-8',    461 from dual union allselect '2015-9',    492 from dual union allselect '2016-1',    376 from dual union allselect '2016-10',    446 from dual union allselect '2016-11',    329 from dual union allselect '2016-12',    434 from dual union allselect '2016-2',    396 from dual union allselect '2016-3',    460 from dual union allselect '2016-4',    551 from dual union allselect '2016-5',    401 from dual union allselect '2016-6',    376 from dual union allselect '2016-7',    454 from dual union allselect '2016-8',    476 from dual union allselect '2016-9',    498 from dual union allselect '2017-1',    408 from dual union allselect '2017-10',    435 from dual union allselect '2017-11',    510 from dual union allselect '2017-12',    462 from dual union allselect '2017-2',    388 from dual union allselect '2017-3',    484 from dual union allselect '2017-4',    408 from dual union allselect '2017-5',    404 from dual union allselect '2017-6',    569 from dual union allselect '2017-7',    398 from dual union allselect '2017-8',    490 from dual union allselect '2017-9',    605 from dual union allselect '2018-1',    389 from dual union allselect '2018-2',    378 from dual union allselect '2018-3',    541 from dual union allselect '2018-4',    392 from dual union allselect '2018-5',    364 from dual union allselect '2018-6',    443 from dual )select * from (  select substr(posted_date, 1, 4) yr, substr(posted_date, instr(posted_date, '-')+1) mnth, sum_claim from claim)pivot(  min(sum_claim) for (mnth) in (1,2,3,4,5,6,7,8,9,10,11,12))order by yr;YR            1          2          3          4          5          6          7          8          9         10         11         12---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------2015                                                                          456        461        492        582        347        4582016        376        396        460        551        401        376        454        476        498        446        329        4342017        408        388        484        408        404        569        398        490        605        435        510        4622018        389        378        541        392        364        443    
    mathguy
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 18, 2018 2:39PM

    Thanks for posting the DDL of your object (i.e. table), but always provide the data as DML statements of your data (i.. insert into table_name).

    I will just use the first two rows, to simulate what I think you want as you didn't show the output you expected.  But since it's pivot I'l assume you want a straight up pivot.

    DDLs

    drop table claim cascade constraints purge;create table claim( posted_date date,claim       number);

    DDLs

    insert into claim values (to_date('2015-10', 'yyyy-mm'), 582);insert into claim values (to_date('2015-11', 'yyyy-mm'), 347);commit;

    SQL

    -- Sum of Volume attributes per Day using PIVOT-- Overallwith datum as     (      select to_char(cl.posted_date, 'yyyy-mm') as my_posted_date            ,sum(cl.claim)  as my_claim        from claim cl       where 1 = 1       group by cl.posted_date     )    ,pivot_datum as     (      select *        from datum d1       pivot (              sum(d1.my_claim)              for my_posted_date in (                                     '2015-10'    as "2015-10"                                    ,'2015-11'    as "2015-11"                                    )             )     )select p1.*  from pivot_datum p1 where 1 = 1;

    OUTPUT

       2015-10    2015-11---------- ----------       582        347
    User_5F8TP
  • User_5F8TP
    User_5F8TP Member Posts: 90 Blue Ribbon
    edited Sep 18, 2018 2:44PM

    I'm sorry for not showing the output. I want the output as following;

    Jul 2017 to Jun 2018Jul 2016 to Jun 2017Jul 2015 to Jun 2016
    JulAugSepOctNovDecJanFebMarAprMayJunTotalAvgJulAugSepOctNovDecJanFebMarAprMayJunTotalAvgJulAugSepOctNovDecJanFebMarAprMayJunTotalAvg
    # by Posted Date398 490 605 435 510 462 389 378 542 392 364 443 5,408 451 454 476 498 446 329 434 408 388 484 408 404 565 5,294 441 527 460 492 582 345 455 372 398 461 552 402 376 5,422 452
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Sep 18, 2018 2:55PM

    Hi,

    841137 wrote:I'm sorry for not showing the output. I want the output as following; Jul 2017 to Jun 2018Jul 2016 to Jun 2017Jul 2015 to Jun 2016 JulAugSepOctNovDecJanFebMarAprMayJunTotalAvgJulAugSepOctNovDecJanFebMarAprMayJunTotalAvgJulAugSepOctNovDecJanFebMarAprMayJunTotalAvg# by Posted Date398 490 605 435 510 462 389 378 542 392 364 443 5,408 451 454 476 498 446 329 434 408 388 484 408 404 565 5,294 441 527 460 492 582 345 455 372 398 461 552 402 376 5,422 452 

    Thanks.  You still need to post INSERT statements for the sample data that produces that output.

    Will you always want exactly one row of output, or will you want multiple rows, one row for every fiscal year(July to June)?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Sep 18, 2018 3:02PM

    Hi,

    If you want a separate row for each fiscal year (and a column that identifies the fiscal year) then you can do something like this:

    WITH    data_to_pivot    AS(    SELECT  EXTRACT (YEAR  FROM  ADD_MONTHS (posted_date, 6))                                              AS fiscal_year    ,       EXTRACT (MONTH FROM posted_date)  AS posted_month    ,       claim    FROM    claim--  WHERE   ...   -- If you want any filtering, put it here)SELECT    *FROM      data_to_pivotPIVOT     (    SUM (claim)          FOR  posted_month  IN (  7  AS jul                                ,  8  AS aug--                              ...                                , 12  AS dec                                ,  1  AS jan--                              ...                                ,  6  AS jun                                )          )ORDER BY  fiscal_year;

    Of course, I can't test this without sample data.

    If you want just 1 row of output, then don't include fiscal_year in the sub-query data_to_pivot.

  • User_5F8TP
    User_5F8TP Member Posts: 90 Blue Ribbon
    edited Sep 18, 2018 3:33PM

    I'm confused as how I can make the heading dynamic of the pivot table instead of hard coding the year and the month. My report shows two years data and all of that should appear on one row.

    select          rd.*,       

    extract(year from payment_date) ||'-'||extract(month from payment_date) posted_date    

    from         rpt_common_data rd

    ),

    rpt_posted as

    ( select posted_date,     

    sum(claim_units) posted_claims

    from rpt_display

    group by posted_date order by posted_date

    ),

    pivot_datnum as

    (  select *    

    from rpt_posted d1      

    pivot (                sum(d1.posted_claims)              

                                  for posted_date in (                          

                                                       '2015-7'    as "2015-7",  

                                                        '2015-8'    as "2015-8",       

                                                         '2015-9'    as "2015-9",      

                                                           '2015-10'  as "2015-10",

                                                            '2015-11'  as "2015-11"   

                                      )

                  ) )

    select p1.*   

    from pivot_datnum p1

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 18, 2018 3:49PM
    841137 wrote:I'm confused as how I can make the heading dynamic of the pivot table instead of hard coding the year and the month. My report shows two years data and all of that should appear on one row.

    Headings should be done via a reporting tool such as SQL* Plus, etc...

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Sep 18, 2018 3:59PM

    Hi,

    841137 wrote:I'm confused as how I can make the heading dynamic of the pivot table instead of hard coding the year and the month. 

    Column names have to known to the compiler before any data is fetched, so it's impossible to have column names that depend on the data found in the same query.

    The best you can do is Dynamic SQL, where you run 2 queries:

    1. a Preliminary Query, to figure out what the column names should be, and
    2. the main query, composed (partly or entirely) of output from the preliminary querry

    (Make sure the table isn't modified between the two queries.)

    My report shows two years data and all of that should appear on one row.

    It looks like 3 years in reply #5.

    select rd.*, extract(year from payment_date) ||'-'||extract(month from payment_date) posted_date 

    If you want strings like '2018-7', it's simpler to use

    TO_CHAR (payment_date, 'YYYY-fmMM')

    from rpt_common_data rd),

    It looks like you didn't post your entire query; I don't see a matching left  (  for the  right  ) above.

    rpt_posted as( select posted_date, sum(claim_units) posted_claimsfrom rpt_displaygroup by posted_date order by posted_date

    There's no point in having an ORDER BY clause in this sub-query.

    (There's rarely any point to having an ORDER BY clause in any sub-query.)

    If you want yearly totals, then use ROLLUP in the GROUP BY query above.

    ),pivot_datnum as ( select * from rpt_posted d1 pivot ( sum(d1.posted_claims)  for posted_date in (  '2015-7' as "2015-7",  '2015-8' as "2015-8", ...

    That's the idea.  Using Dynamic SQL, the preliminary query would write the 2 lines immediately above, and however many more like them you need.

This discussion has been closed.