Transpose rows into columns

Pavan Kumar

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

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

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

shrnxt

Thanks Pawan

I will give it a try tommorrow.

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

Pavan Kumar

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

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

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.

shrnxt

Thanks Babara..

Barbara Boehmer

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

Thanks Barbara for helping me out.

This was exactly what I was looking for.

Thanks once again.