Home » SQL & PL/SQL » SQL & PL/SQL » Oracle reports (Oracle 11g)
| Oracle reports [message #653383] |
Mon, 04 July 2016 08:36  |
srinivas.k2005
Messages: 404
Registered: August 2006
|
Senior Member |
|
|
Create table x_test (
invoice_sys_id varchar2(50),
CURRENT_STATE varchar2(20),
rec_create_date date,
user_id varchar2(50)
);
Insert into x_test values ('101','inprocess',to_date('02-09-2016','mm-dd-yyyy'),'John');
Insert into x_test values ('101','approved',to_date('02-15-2016','mm-dd-yyyy'),'Warner');
Insert into x_test values ('101','rejected',to_date('02-20-2016','mm-dd-yyyy'),'Mark');
Insert into x_test values ('101','approved',to_date('02-25-2016','mm-dd-yyyy'),'Jenny');
Insert into x_test values ('101','escalated',to_date('02-26-2016','mm-dd-yyyy'),'Tom');
Insert into x_test values ('101','approved',to_date('02-28-2016','mm-dd-yyyy'),'Bent');
Insert into x_test values ('201','inprocess',to_date('04-09-2016','mm-dd-yyyy'),'Bianca');
Insert into x_test values ('201','approved',to_date('04-15-2016','mm-dd-yyyy'),'Andrew');
Insert into x_test values ('201','rejected',to_date('04-20-2016','mm-dd-yyyy'),'Jaco');
Insert into x_test values ('201','approved',to_date('04-25-2016','mm-dd-yyyy'),'Tracy');
Insert into x_test values ('201','escalated',to_date('04-26-2016','mm-dd-yyyy'),'Kiran');
Insert into x_test values ('201','approved',to_date('04-28-2016','mm-dd-yyyy'),'Rooney');
SQL> select * from x_test order by invoice_sys_id,rec_create_date;
INVOICE_SYS_ID CURRENT_STATE REC_CREATE_DATE USER_ID
-------------------------------------------------- -------------------- --------------- --------------------------------------------------
101 inprocess 2/9/2016 John
101 approved 2/15/2016 Warner
101 rejected 2/20/2016 Mark
101 approved 2/25/2016 Jenny
101 escalated 2/26/2016 Tom
101 approved 2/28/2016 Bent
201 inprocess 4/9/2016 Bianca
201 approved 4/15/2016 Andrew
201 rejected 4/20/2016 Jaco
201 approved 4/25/2016 Tracy
201 escalated 4/26/2016 Kiran
201 approved 4/28/2016 Rooney
12 rows selected
Requirement:
I should retrieve only those values which are in 'approved' status for each INVOICE_SYS_ID.
And top of this requirement, values of user_id which are in 'approved' status should be represented in columns and the order of columns is based on REC_CREATE_DATE
Result should be something like,
INVOICE_SYS_ID CURRENT_STATE USER_ID_CURRENT_STATE_Approve1 USER_ID_CURRENT_STATE_Approve2 USER_ID_CURRENT_STATE_Approve3
101 approved Warner Jenny Bent
201 approved Andrew Tracy Rooney
Also, please note the number of records in x_test table is dynamic.
Please advise.
Regards,
SRK
[mod-edit: code tags added by bb]
[Updated on: Mon, 04 July 2016 20:47] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Oracle reports [message #653388 is a reply to message #653384] |
Mon, 04 July 2016 09:15  |
srinivas.k2005
Messages: 404
Registered: August 2006
|
Senior Member |
|
|
Thank you:) I am getting results as below. But I am just trying to get the details of UserId in different columns!!
SQL> select invoice_sys_id,userid_Details
2 From
3 (
4 SELECT invoice_sys_id,
5 LISTAGG(USER_ID, '; ')
6 WITHIN GROUP (ORDER BY REC_CREATE_DATE) userid_Details,
7 MIN(REC_CREATE_DATE)
8 FROM x_test
9 where CURRENT_STATE='approved'
10 Group by invoice_sys_id
11 )
12 ;
INVOICE_SYS_ID USERID_DETAILS
-------------------------------------------------- --------------------------------------------------------------------------------
101 Warner; Jenny; Bent
201 Andrew; Tracy; Rooney
[mod-edit: code tags added by bb]
[Updated on: Mon, 04 July 2016 20:49] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Oracle reports [message #653392 is a reply to message #653390] |
Mon, 04 July 2016 09:42 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Since you've got a variable number of users per invoice_sys_id you'll need a variable number of columns (or use more columns than you'll ever actually need). Do you really need the users in separate columns?
|
|
|
|
| Re: Oracle reports [message #653411 is a reply to message #653388] |
Mon, 04 July 2016 21:14 |
 |
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In the example below, where it says "for rn in (1, 2, 3)", you just need to make sure that you include enough numbers to go to the maximum possible number of columns. You can make the column name whatever you like. I just used "Approve" so that it would display in one line on the forums for easier reading.
-- test data:
SCOTT@orcl_12.1.0.2.0> column invoice_sys_id format a14
SCOTT@orcl_12.1.0.2.0> column user_id format a7
SCOTT@orcl_12.1.0.2.0> select * from x_test
2 /
INVOICE_SYS_ID CURRENT_STATE REC_CREATE USER_ID
-------------- -------------------- ---------- -------
101 inprocess 09/02/2016 John
101 approved 15/02/2016 Warner
101 rejected 20/02/2016 Mark
101 approved 25/02/2016 Jenny
101 escalated 26/02/2016 Tom
101 approved 28/02/2016 Bent
201 inprocess 09/04/2016 Bianca
201 approved 15/04/2016 Andrew
201 rejected 20/04/2016 Jaco
201 approved 25/04/2016 Tracy
201 escalated 26/04/2016 Kiran
201 approved 28/04/2016 Rooney
12 rows selected.
-- query:
SCOTT@orcl_12.1.0.2.0> column "1_Approve" format a15
SCOTT@orcl_12.1.0.2.0> column "2_Approve" format a15
SCOTT@orcl_12.1.0.2.0> column "3_Approve" format a15
SCOTT@orcl_12.1.0.2.0> select *
2 from (select invoice_sys_id, current_state, user_id,
3 row_number () over
4 (partition by invoice_sys_id, current_state
5 order by rec_create_date) rn
6 from x_test
7 where current_state = 'approved')
8 pivot (max(user_id) as "Approve" for rn in (1, 2, 3))
9 /
INVOICE_SYS_ID CURRENT_STATE 1_Approve 2_Approve 3_Approve
-------------- -------------------- --------------- --------------- ---------------
101 approved Warner Jenny Bent
201 approved Andrew Tracy Rooney
2 rows selected.
|
|
|
|
| Re: Oracle reports [message #653421 is a reply to message #653411] |
Tue, 05 July 2016 03:17 |
srinivas.k2005
Messages: 404
Registered: August 2006
|
Senior Member |
|
|
Thanks Michel,Cookiemonster and Barbara:)
@cookiemonster/Barbara
I will propose two solutions to my client.
1. Values in single column separated by comma.
SQL> select invoice_sys_id,userid_Details
2 From
3 (
4 SELECT invoice_sys_id,
5 LISTAGG(USER_ID, '; ')
6 WITHIN GROUP (ORDER BY REC_CREATE_DATE) userid_Details,
7 MIN(REC_CREATE_DATE)
8 FROM x_test
9 where CURRENT_STATE='approved'
10 Group by invoice_sys_id
11 )
12 ;
INVOICE_SYS_ID USERID_DETAILS
-------------------------------------------------- --------------------------------------------------------------------------------
101 Warner; Jenny; Bent
201 Andrew; Tracy; Rooney
2. Include maximum number of columns for useridapprover.For bew example, I have done till 20.
SQL> select *
2 from (select invoice_sys_id, current_state, user_id,
3 row_number () over
4 (partition by invoice_sys_id, current_state
5 order by rec_create_date) rn
6 from x_test
7 where current_state = 'approved')
8 pivot (max(user_id) as "Approve" for rn in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20))
9 ;
INVOICE_SYS_ID CURRENT_STATE 1_Approve 2_Approve 3_Approve 4_Approve 5_Approve 6_Approve 7_Approve 8_Approve 9_Approve 10_Approve 11_Approve 12_Approve 13_Approve 14_Approve 15_Approve 16_Approve 17_Approve 18_Approve 19_Approve 20_Approve
-------------------------------------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
101 approved Warner Jenny Bent
201 approved Andrew Tracy Rooney
Hopefully, everything would be fine. Thanks again.
Regards,
SRK
[mod-edit: code tags added by bb; please add the code tags yourself in the future]
[Updated on: Tue, 05 July 2016 15:20] by Moderator Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 15:31:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
