Forum Stats

  • 3,855,352 Users
  • 2,264,499 Discussions
  • 7,905,976 Comments

Discussions

Pivot in oracle

User_5F8TP
User_5F8TP Member Posts: 90 Blue Ribbon
edited Dec 18, 2018 4:09PM in SQL & PL/SQL

Following is my code snippet.

select distinct *
from
         rpt_pivot_data
             pivot (
                     sum(numeric_6)
                     for (numeric_3)
                     in ('Q1' as "1Q",
                         'Q2' as "2Q",
                         'Q3' as "3Q",
                         'Q4' as "4Q",
                         'no_show_Q1',
                         'no_show_Q2',
                         'no_show_Q3',
                         'no_show_Q4',
                         'ytd' as "YTD")
                    )

I want the 'no_show_Q1' to appear under 1Q,'no_show_Q2' to appear under 2Q, 'no_show_Q3' to appear under 3Q, and 'no_show_Q4' to appear under 4Q.

Please suggest as to how I can accomplish this task. Thank you in advance!

I'm not providing with the data or tables thinking there might be a quick answer to my question. Please let me know if I still need to provide tables and data.

Thanks!

Tagged:
Frank KulashUser_5F8TP

Best Answer

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Dec 18, 2018 3:22PM Answer ✓

    In any case - since you want to combine different values of NUMERIC_3 into single columns in the output, it may be best to use the old method of pivoting: Conditional aggregation.

    Something like this:

    select  sum(case when NUMERIC_3 in ('Q1', 'no_show_Q1') then NUMERIC_6 end) as "1Q",  .....from  .....GROUP BY (the other columns)
    User_5F8TPUser_5F8TP

Answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Dec 18, 2018 3:13PM
    841137 wrote:I'm not providing with the data or tables thinking there might be a quick answer to my question. Please let me know if I still need to provide tables and data.Thanks!

    Then you're mistaken by not providing the data.  By default provide tables and data unless it's a trivial question.  Saves you time.  We don't have your environment to give you a "quick" answer you think.

    Just my opinion

    Frank Kulash
  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Dec 18, 2018 3:18PM

    Your code snippet doesn't match the image.

    You select * which means all columns. Your output has several rows, which means the pivoting must be grouping by some column or columns other than NUMERIC_6 and NUMERIC_3. If there is such an additional column (or more than one), you can still get the result with just the columns you show, but then it can't be SELECT *.

    Another question - why select DISTINCT? Pivoting is an aggregation anyway, so you can't get duplicates (unless, indeed, you are not selecting ALL the columns; that is, you are not selecting the columns by which you group).

    And when you say "under", do you mean in those columns, or do you actually mean "at the bottom" (in which case of course you would need to state the ordering criterion).

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

    Hi,

    841137 wrote:Following is my code snippet.select distinct *
    from
    rpt_pivot_data
    pivot (
    sum(numeric_6)
    for (numeric_3)
    in ('Q1' as "1Q",
    'Q2' as "2Q",
    'Q3' as "3Q",
    'Q4' as "4Q",
    'no_show_Q1',
    'no_show_Q2',
    'no_show_Q3',
    'no_show_Q4',
    'ytd' as "YTD")
    )

    I want the 'no_show_Q1' to appear under 1Q,'no_show_Q2' to appear under 2Q, 'no_show_Q3' to appear under 3Q, and 'no_show_Q4' to appear under 4Q.

    Please suggest as to how I can accomplish this task. Thank you in advance!

    I'm not providing with the data or tables thinking there might be a quick answer to my question. Please let me know if I still need to provide tables and data.

    Thanks!

    Yes; always provide CREATE TABLE and INSERT statements (or equivalent) for the sample data, and the exact results you want from that data.  Where do you want the YTD value?

    Right now, you're getting 9 columns of output, corresponding to the 9 possible values of numeric_3.

    Derive two columns from numeric_3: one that has 5 possible values ('Q1', 'Q2', 'Q3', 'Q4' and 'YTD'), and the other that has 2 possible values ('regular' and 'no show').  Pivot on the new column that has 5 values.

  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Dec 18, 2018 3:22PM Answer ✓

    In any case - since you want to combine different values of NUMERIC_3 into single columns in the output, it may be best to use the old method of pivoting: Conditional aggregation.

    Something like this:

    select  sum(case when NUMERIC_3 in ('Q1', 'no_show_Q1') then NUMERIC_6 end) as "1Q",  .....from  .....GROUP BY (the other columns)
    User_5F8TPUser_5F8TP
  • User_5F8TP
    User_5F8TP Member Posts: 90 Blue Ribbon
    edited Dec 18, 2018 4:09PM

    Thank you so much. Your answer is what I needed.

    I did the following logic before pivoting, and was able to bring the no_show below each quarters

    case when numeric_3 in ('Q1','no_show_Q1') then 'Q1'

                         when numeric_3 in ('Q2','no_show_Q2') then 'Q2'

                         when numeric_3 in ('Q3','no_show_Q3') then 'Q3'

                         when numeric_3 in ('Q4','no_show_Q4') then 'Q4'

                    else numeric_3 end numeric_3 

    Thank you so much once again!!!!!pastedImage_0.png