SQL & PL/SQL

What version of the Oracle database are you running?

Oracle 11g

Then, take a look at the PILOT operator

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

select * from table (pivot  ('select process_status

                      from  sla_status

                      group by  process_name, process_date'))

I tried the above query but to no avail.

Hi shrnxt,

There are lot's of thread transpose rows into columns.

Check this may help you.

You can also search on forum.

Post your create table and insert statement script with your expected sample output you want to display.

SQL> with data as       
 



(
select  'A' as Client, 'B' as Process, '21-june' as Date1, 'Y' as Status from dual union all
select  'C' as Client, 'D' as Process, '22-june' as Date1, 'N' as Status from dual union all
select  'A' as Client, 'B' as Process, '22-june' as Date1, 'N' as Status from dual union all
select  'C' as Client, 'D' as Process, '21-june' as Date1, 'Y' as Status from dual 
)
select *
from data



pivot(  max(status) for Date1 in ('21-june' as "21-june", '22-june' as "22-june"))  2
3
4
5
6
7
8
9   10  ;




C P 2 2
- - - -
A B Y N
C D Y N

Hope it helps

- Pavan Kumar N

Hi

I checked the articles, but none deal with dates.

Also I wish to display status and not averages or sum

Hi Praveen

Thanks for the reply.

Process status is string

I tried the following query but to no avail:

select client_operator, process_name,process_date, process_status from table

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

Hi

I was able to get the ouput in Oracle Apex 5 by using pivot feature of interactive report.

I am attaching the screenshot :

Can someone help me with the query.

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

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');

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

Thanks Pawan

I will give it a try tommorrow.

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

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

Refer to that link

- Pavan Kumar N

Thanks Pawan

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

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

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

SCOTT@orcl_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.

SCOTT@orcl_12.1.0.2.0> variable g_ref refcursor

SCOTT@orcl_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.

SCOTT@orcl_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.

Thanks Babara..

Thanks Barbara for helping me out.

This was exactly what I was looking for.

Thanks once again.