Forum Stats

  • 3,875,405 Users
  • 2,266,911 Discussions
  • 7,912,196 Comments

Discussions

converting rows to columns

Bommi
Bommi Member Posts: 721 Bronze Badge

Hi Experts,

We have a query which fetches data as below

Now, we need to convert that data as rows to columns and the final output we required should be as below

Can anyone please help me on how to achieve this.

Please note:

  1. The actual data(as shown in first image) is from a query, not a direct table data.
  2. We have to achieve this only through SQL(please don't ask me why only though query :-) )
  3. I already tried using PIVOT/UNPIVOT. But seems, to use those we need to have a aggregate functions. But in my case, it is purely converting rows to columns blindly.
  4. The PERIOD_FROM and VALUE are not fixed(Our client company's current period starts from 30-Oct. And we need to fetch current + future 6 months data which is as show in images)

Awaiting for your help

Thanks in Advance,

Bommi

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,779 Red Diamond

    Hi, @Bommi

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so 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 explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    Does the number of columns in the result set depend on what data you find in the table? Does the result set have only one row of output, and the line labeled "PERIOD_FROM" is a header? If either answer is "Yes", then you need dynamic SQL. Do you actually need separate columns in the output, or would you be satisfied with a big string column, formatted so it looks like separate columns? If so, you can avoid dynamic SQL, as shown in this thread:

    PIVOT SQL — oracle-tech

    I already tried using PIVOT/UNPIVOT. But seems, to use those we need to have a aggregate functions. But in my case, it is purely converting rows to columns blindly.

    In that case, either MIN or MAX can be used as the aggregate function.

  • Bommi
    Bommi Member Posts: 721 Bronze Badge

    Hi @Frank Kulash

    Attached the actual query

    As I explained in quey, we need to fetch for 6 months data and which means the data will vary based on month the current date falls. Also, once you see the query, you will get to know it is Cloud requirement :-)

    The reason why we need Rows to Columns: w are creating a .csv file where it has complete info for each item(like, item, region, Site, Org, Booking Forecast for 6 months etc.,) and sending that file to other system


    Regards,

    Bommi

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,779 Red Diamond

    Hi, @Bommi

    Post everything (including the CREATE TABLE and INSERT statements for the sample data and the desired results) right here in this space. Don't use attachments; not everyone can or will open attachments.

    we need to fetch for 6 months data and which means the data will vary based on month the current date falls.

    That's good; then you won't need dynamic SQL. If the data represents 6 months, why are there only 2 distinct values for period_from?

  • Bommi
    Bommi Member Posts: 721 Bronze Badge

    Hi @Frank Kulash ,

    There is one more column before those 2, which is Org. The Org is different for all those 6 columns. As I removed that org from sample data I shared, you may be feeling as only 2 distinct values. The below is actual data


    But, my question is: 'converting rows to columns' is possible(We need only Period_From and Value columns only)?


    Regards,

    Bommi

  • Stax
    Stax Member Posts: 184 Silver Badge

    hi Bommi 

    use dynamic SQL, pivot XML,sql macro, ...

    for example only

    with t (Period_From, Value) as (
    select date '2022-01-11',232.323 from dual union all
    select date '2022-02-22',732.567 from dual union all
    select date '2022-01-11',678.632 from dual union all
    select date '2022-02-12',123.312 from dual union all
    select date '2022-11-11',967.098 from dual )
    ,u as (
     select * from (select rownum rn,to_char(Period_From,'dd.mm.yyyy') Period_From,to_char(value) value from t) 
     UNPIVOT (s FOR f IN (Period_From AS 'Period_From',Value AS 'Value'))
    )
    select * from u
    pivot (max(s)  col for rn in (1 "1", 2 "2", 3 "3", 4 "4", 5 "5", 6 "6", 7 "7", 8 "8", 9 "9", 10 "10" ))
    /
    
    
    F           1_COL                                    2_COL                                    3_COL                                    4_COL                                    5_COL                                    6_COL                                    7_COL                                    8_COL                                    9_COL                                    10_COL
    ----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
    Value       232.323                                  732.567                                  678.632                                  123.312                                  967.098
    Period_From 11.01.2022                               22.02.2022                               11.01.2022                               12.02.2022                               11.11.2022
    
    
    
    Bommi
  • Bommi
    Bommi Member Posts: 721 Bronze Badge

    Hi @User_3ABCE , @Stax

    Thanks for your valuable inputs 😀

    I will check with those and will let you know

    Thanks,

    Bommi