For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
I have a table like:
Client Process Date Status
A B 21-june Y
C D 22-june N
A B 22-june N
C D 21-june Y
..
n rows
I want to display a report in the following format:
Client Process 21-June 22-june .. .... n colums
A B Y N
C D Y N
Please help...
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.
(
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
C P 2 2
- - - -
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)))
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
2. You need to alias it
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
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
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
--------------- --------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
2 rows selected.
Thanks Babara..
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):
SCOTT@orcl_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
Thanks Barbara for helping me out.
This was exactly what I was looking for.
Thanks once again.