Create table trans_details(code varchar2(4),pid integer(10),tid integer(2),reg_date date,expiry_date date,transdate date)
Insert into trans_details values (‘abcd’,1,10,01-01-1988,12-01-1989,30-12-1987)
Insert into trans_details values (‘abcd’,1,11,01-01-1988,12-01-1989,11-01-1989)
So these records should be loaded into target as one record.
So I need the data to look like
Abcd,1,01-01-1988,12-01-1989,30-12-1987,01-01-1988,12-01-1989,11-01-1989
So the two entries related to tid 10 and 11 must be combined and I will take the required columns based on the logic.
I am thinking it can be done with pivot, but I am not able to implement this. Please could some one give me the sample code.
Thank you
Looks like you need listagg, either standalone or within a pivot:
Create table trans_details(code varchar2(4),pid integer,tid integer,reg_date date,expiry_date date,transdate date);
alter session set nls_date_format = 'dd-mm-yyyy';
Insert into trans_details values ('abcd',1,10,'01-01-1988','12-01-1989','30-12-1987');
Insert into trans_details values ('abcd',1,11,'01-01-1988','12-01-1989','11-01-1989');
select code, pid,
listagg(reg_date || ',' || expiry_date || ',' || transdate, ',')
within group (order by tid) dts
from trans_details
group by code, pid;
CODE PID DTS
abcd 1 01-01-1988,12-01-1989,30-12-1987,01-01-1988,12-01-1989,11-01-1989
select * from trans_details
pivot (
listagg(reg_date, ',') within group (order by tid) regdts,
listagg(expiry_date, ',') within group (order by tid) expdts,
listagg(transdate, ',') within group (order by tid) transdts
for tid in (10, 11)
);
CODE PID 10_REGDTS 10_EXPDTS 10_TRANSDTS 11_REGDTS 11_EXPDTS 11_TRANSDTS
abcd 1 01-01-1988 12-01-1989 30-12-1987 01-01-1988 12-01-1989 11-01-1989 You can read more about pivoting at:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot