Hi,
I would like to transpose some rows to columns that belong to an id. The order of the transposed columns should be based on the process date descending.
Below is the sample data:
create table test(
id number,
process_date date,
cust_name varchar2(20),
curr_add_1 varchar2(20),
old_add_1 varchar2(20),
curr_add_2 varchar2(20),
old_add_2 varchar2(20),
curr_add_3 varchar2(20),
old_add_3 varchar2(20))
insert into test values(001, '01-JAN-2000', 'GYB Ltd', 'Hugh Farm', 'Connell House', 'Regent Street', 'Downington', 'Hazlemere', 'Herts');
insert into test values(001, '28-MAR-2000', 'GYB Ltd', 'Hugh Farm', '14 Connell House', 'Regent Street', 'Downington', 'Hazlemere', 'Herts);
insert into test values(002, '23-JUN-2012', 'RSG Ltd', 'Unit 8', 'Hurst House', 'Hind Road', 'Cumbria', 'Merrow', '');
insert into test values(002, '28-SEP-2013', 'RSG Ltd', 'Unit 14', 'Hurst House', 'Hind Road', 'Cumbria', 'Merrow', '');
insert into test values(002, '12-JAN-2014', 'RSG Ltd', 'Unit 14', 'Hurst House', 'Hind Road', 'Cumbria', 'Merrow', 'Herts');
Expected Output:
Columns:
ID - 001
CURR_ADD_1_1 = 'Hugh Farm'
OLD_ADD_1_1 = 'Connell House'
CURR_ADD_2_1 = 'Regent Street'
OLD_ADD_2_1 = 'Downington'
CURR_ADD_3_1 = 'Hazlemere'
OLD_ADD_3_1 = 'Herts'
CURR_ADD_1_2 = 'Hugh Farm'
OLD_ADD_1_2 = '14 Connell House'
CURR_ADD_2_2 = 'Regent Street'
OLD_ADD_2_2 = 'Downington'
CURR_ADD_3_2 = 'Hazlemere'
OLD_ADD_3_2 = 'Herts'
ID - 002
CURR_ADD_1_1
OLD_ADD_1_1
CURR_ADD_2_1
OLD_ADD_2_1
CURR_ADD_3_1
OLD_ADD_3_1
CURR_ADD_1_2
OLD_ADD_1_2
CURR_ADD_2_2
OLD_ADD_2_2
CURR_ADD_3_2
OLD_ADD_3_2
CURR_ADD_1_3
OLD_ADD_1_3
CURR_ADD_2_3
OLD_ADD_2_3
CURR_ADD_3_3
OLD_ADD_3_3
Message was edited by: sliderrules