Aggregate multiple rows of data into 1 column and insert into multiple columns

There will be scenario where we want to extract multiple rows data into one column and may insert column data for multiple columns in a single or multiple columns. 
This can be done using listagg and substr functions using oracle.

Below SQL will extract multiple rows of compensation in a single column, each value separated by delimiter comma (,).

 SELECT COUNT(*) c
,comp.emplid
,comp.effdt
,COMP.EMPL_RCD
,COMP.EFFSEQ
, listagg (comp.comp_ratecd||','||comp.comprate, ',') WITHIN
GROUP (
ORDER BY comp.comp_ratecd) comprates
FROM PS_COMPENSATION COMP
, PS_COMP_RATECD_TBL B
WHERE COMP.COMP_RATECD= B.COMP_RATECD
AND B.EFF_STATUS = 'A'
AND B.COMP_BASE_PAY_SW ='N'
AND B.EFFDT=(
SELECT MAX(B1.EFFDT)
FROM PS_COMP_RATECD_TBL B1
WHERE B.COMP_RATECD=B1.COMP_RATECD
AND b1.effdt <=COMP.EFFDT)
GROUP BY comp.emplid,comp.effdt,COMP.EMPL_RCD,COMP.EFFSEQ

And using substr and instr we can split one column data to multiple columns.
Below sql will show how multiple rows of data are extracted into 1 column and insert into multiple columns (10 columns).
SELECT A.EMPLID 
 ,A.EFFDT 
 ,a.EMPL_RCD 
 ,A.EFFSEQ 
 , NVL((CASE WHEN A.C >= '1' THEN substr(a.comprates  ,1 
 ,instr(a.comprates ,','  ,1  ,1)-1) ELSE '' END)  ,' ') COL1 
 , NVL((CASE WHEN A.C=0 THEN '' 
WHEN A.C=1 THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,1)+1 
 ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1  ,1)+1)
 WHEN A.C > '1' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,1)+1  ,instr(a.comprates  ,','  ,1  ,2)-(instr(a.comprates ,','  ,1  ,1)+1)) END)  ,0) COL2 
 , NVL((CASE WHEN A.C<=1 THEN '' 
WHEN A.C >= '2' THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,2)+1  ,instr(a.comprates  ,','  ,1  ,3)-(instr(a.comprates ,',' ,1 
 ,2)+1)) END)  ,' ') COL3 
 , NVL((CASE WHEN A.C=0 THEN '' 
WHEN A.C=1 THEN '' WHEN A.C ='2' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,3)+1  ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1 
 ,3)+1) 
WHEN A.C > '2' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,3)+1 
 ,instr(a.comprates  ,','  ,1  ,4)-(instr(a.comprates  ,','  ,1  ,3)+1)) END)  ,0) COL4 
 , NVL((CASE 
WHEN A.C<=2 THEN '' WHEN A.C >= '3' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,4)+1  ,instr(a.comprates  ,','  ,1  ,5)-(instr(a.comprates  ,','  ,1 
 ,4)+1)) END)  ,' ') COL5 
 , NVL((CASE 
WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C ='3' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,5)+1  ,LENGTH(a.comprates)-instr(a.comprates 
 ,','  ,1  ,5)+1) 
WHEN A.C > '3' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,5)+1  ,instr(a.comprates  ,','  ,1  ,6)-(instr(a.comprates  ,','  ,1  ,5)+1)) END)  ,0) COL6 
 , NVL((CASE WHEN A.C<=3 THEN '' 
WHEN A.C >= '4' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,6)+1  ,instr(a.comprates  ,','  ,1  ,7)-(instr(a.comprates  ,','  ,1 
 ,6)+1)) END)  ,' ') COL7 
 , NVL((CASE WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C=3 THEN '' WHEN A.C ='4' THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,7)+1  ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1  ,7)+1) 
WHEN A.C > '4' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,7)+1  ,instr(a.comprates 
 ,','  ,1  ,8)-(instr(a.comprates  ,','  ,1  ,7)+1)) END)  ,0) COL8 
 , NVL((CASE WHEN A.C<=4 THEN '' WHEN A.C >= '5' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,8)+1  ,instr(a.comprates  ,','  ,1  ,9)-(instr(a.comprates  ,','  ,1  ,8)+1)) END)  ,' ') COL9 
 , NVL((CASE WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C=3 THEN '' WHEN A.C=4 THEN '' WHEN A.C >= '5' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,9)+1  ,9) END)  ,0) COL10 
  FROM ( 
 SELECT COUNT(*) c 
 ,comp.emplid 
 ,comp.effdt 
 ,COMP.EMPL_RCD 
 ,COMP.EFFSEQ 
 , listagg (comp.comp_ratecd||','||comp.comprate 
 , ',') WITHIN 
  GROUP ( 
  ORDER BY comp.comp_ratecd) comprates 
  FROM PS_COMPENSATION COMP 
  , PS_COMP_RATECD_TBL B 
 WHERE COMP.COMP_RATECD= B.COMP_RATECD 
   AND B.EFF_STATUS = 'A' 
   AND B.COMP_BASE_PAY_SW ='N' 
   AND B.EFFDT=( 
 SELECT MAX(B1.EFFDT) 
  FROM PS_COMP_RATECD_TBL B1 
 WHERE B.COMP_RATECD=B1.COMP_RATECD 
   AND b1.effdt <=COMP.EFFDT) 
  GROUP BY comp.emplid,comp.effdt,COMP.EMPL_RCD,COMP.EFFSEQ) A, PS_xx_stage_tbl STG 
 WHERE a.emplid=STG.EMPLID 
   AND a.EFFDT=STG.EFFDT 
   AND A.empl_rcd = STG.empl_rcd 
   AND A.effseq = STG.effseq