Forum Stats

  • 3,873,344 Users
  • 2,266,537 Discussions
  • 7,911,514 Comments

Discussions

Transpose rows into columns

2»

Answers

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jun 25, 2016 6:54AM

    Hi,

    1. Please provide the required inputs statements --- since as per your earlier example I have provided the output

    (pivot(max(status) for process_date in (select process_date from table)))
    

    2. You need to alias it

    - Pavan Kumar N

  • shrnxt
    shrnxt Member Posts: 22
    edited Jun 25, 2016 7:46AM
    CREATE TABLE  "SLA_STATUS" 
       (     "CLIENT_OPERATOR" VARCHAR2(100),
         "PROCESS_NAME" VARCHAR2(100),
         "PROCESS_TICK" VARCHAR2(20),
         "PROCESS_DATE" DATE
       )
    /

    Insert into sla_status values ('A','B','Y','06/21/2016');

    Insert into sla_status values ('C',D','N','06/22/2016');

    Insert into sla_status values ('A','B','Y','06/22/2016');

    Insert into sla_status values ('C','D',N','06/21/2016');

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jun 25, 2016 8:17AM
    SQL> select *
    from (select * from sla_status)
    pivot(  max(PROCESS_TICK) for PROCESS_DATE in (to_date('06/21/2016','MM/DD/YYYY'), to_date('06/22/2016','MM/DD/YYYY')))  2    3  ;
    
    CLIENT_OPE PROCESS_NA TO_DATE('06/21/2016' TO_DATE('06/22/2016'
    ---------- ---------- -------------------- --------------------
    A       B          Y            Y
    C       D          N            N
    
    
    shrnxtshrnxt
  • shrnxt
    shrnxt Member Posts: 22
    edited Jun 25, 2016 9:09AM

    Thanks Pawan

    I will give it a try tommorrow.

    I dont need hardcoded dates. Will processdate between sysdate and sysdate-7 work?

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jun 25, 2016 9:13AM

    Probably you need to get it in dynamic way -- you need to use plsql @BluShadow demoed it

    Refer to that link

    - Pavan Kumar N

  • shrnxt
    shrnxt Member Posts: 22
    edited Jun 25, 2016 10:46PM

    Thanks Pawan

    This query works, but I want it to be dynamic.

    Can you please send me the link or an alternative way.

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    edited Jun 26, 2016 12:05AM

    The following dynamically generates the query, displays the generated query, then displays the results of the query.

    [email protected]_12.1.0.2.0> select * from sla_status

      2  /

    CLIENT_OPERATOR PROCESS_NAME    PROCESS_TICK        PROCESS_DA

    --------------- --------------- -------------------- ----------

    A              B              Y                    06/21/2016

    C              D              N                    06/22/2016

    A              B              Y                    06/22/2016

    C              D              N                    06/21/2016

    4 rows selected.

    [email protected]_12.1.0.2.0> variable g_ref refcursor

    [email protected]_12.1.0.2.0> declare

      2    v_sql  clob;

      3  begin

      4    v_sql := 'select * from sla_status pivot(max(process_tick) for process_date in (';

      5    for r in (select sysdate - (8 - rownum + 1) dateval from dual connect by level <= 8) loop

      6      v_sql := v_sql || '''' || r.dateval || ''' as "' || r.dateval || '",';

      7    end loop;

      8    v_sql := rtrim (v_sql, ',') || '))';

      9    dbms_output.put_line (v_sql);

    10    open :g_ref for v_sql;

    11  end;

    12  /

    select * from sla_status pivot(max(process_tick) for process_date in ('06/17/2016' as "06/17/2016",'06/18/2016' as "06/18/2016",'06/19/2016' as "06/19/2016",'06/20/2016' as "06/20/2016",'06/21/2016'

    as "06/21/2016",'06/22/2016' as "06/22/2016",'06/23/2016' as "06/23/2016",'06/24/2016' as "06/24/2016"))

    PL/SQL procedure successfully completed.

    [email protected]_12.1.0.2.0> print g_ref

    CLIENT_OPERATOR PROCESS_NAME    06/17/2016          06/18/2016          06/19/2016          06/20/2016          06/21/2016          06/22/2016          06/23/2016          06/24/2016

    --------------- --------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------

    A              B                                                                                                  Y                    Y

    C              D                                                                                                  N                    N

    2 rows selected.

    Kalpatarushrnxt
  • shrnxt
    shrnxt Member Posts: 22
    edited Jun 26, 2016 1:53AM

    Thanks Babara..

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    edited Jun 26, 2016 10:05PM Answer ✓

    Here is another option that enables you to return a result set from SQL using a pipelined function:

    Download Anton Scheffer's dynamic pivot function from https://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip

    Unzip it and run pivotfun.sql to create the funciton.

    I edited the function to change the name from pivot to pivotfun to avoid conflict with an existing Oracle name

    and commented out the dbms_output lines from the object before running it.

    When you use it, it requires that you provide an alias for the aggregate in the query that you pass to it.

    Then you can do the following (note that I used mpt as an alias for the aggregate):

    [email protected]_12.1.0.2.0> select *

      2  from  table (pivotfun (

      3          'select client_operator, process_name, process_date, max (process_tick) mpt

      4          from  (select client_operator, process_name, process_tick, process_date

      5              from  sla_status

      6              union all

      7              select client_operator, process_name, to_char (null) process_tick, process_date

      8              from  (select distinct client_operator, process_name

      9                  from  sla_status),

    10                  (select trunc (sysdate) - (8 - rownum + 1) process_date

    11                  from  dual connect by level <= 8))

    12          group  by client_operator, process_name, process_date' ) )

    13  order  by client_operator, process_name

    14  /

    CLIENT_OPERATOR PROCESS_NAME    06/18/2016          06/19/2016          06/20/2016          06/21/2016          06/22/2016          06/23/2016          06/24/2016          06/25/2016

    --------------- --------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------

    A              B                                                                              Y                    Y

    C              D                                                                              N                    N

    2 rows selected.

  • shrnxt
    shrnxt Member Posts: 22
    edited Jun 27, 2016 8:53AM

    Thanks Barbara for helping me out.

    This was exactly what I was looking for.

    Thanks once again.

This discussion has been closed.