Forum Stats

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

Discussions

Categories

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

    · Share on TwitterShare on Facebook
  • 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');
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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
      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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?
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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.
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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
      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
shrnxt 
     
    

shrnxt 

Member Posts: 22 

    

    

 
 edited Jun 26, 2016 1:53AM  
    

     
    
 
    

    

    

    

    

    Thanks Babara..
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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.
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 

  • 

    

    
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.
     
    

      · Share on TwitterShare on Facebook
     
    

    

    

    • 



 


This discussion has been closed.