Peter Andersen
I am always getting this question so I thought I would share this example with you in case it comes up and you need a quick answer.
The comments are the titles of each code block. This is not the only way to do this, it is just the way I have done it most recently.
Here's a simple example of taking multiple rows and summarizing them on 1 row. This is even usable in PS QUERY.
Flattening a File
SELECT A.EMPLID , A.INSTITUTION , A.ACAD_CAREER
– put in a string with listagg
,(LISTAGG(A.STDNT_CAR_NBR,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) STDNT_CAR_NBR ,(LISTAGG(A.ACAD_PROG,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) ACAD_PROG ,(LISTAGG(B.ACAD_PLAN,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) ACAD_PLAN ,(LISTAGG(B.PLAN_SEQUENCE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) PLAN_SEQUENCE ,(LISTAGG(C.ACAD_PLAN_TYPE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) ACAD_PLAN_TYPE ,(LISTAGG(A.PROG_STATUS,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) PROG_STATUS ,(LISTAGG(C.DEGREE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) DEGREE ,(LISTAGG(D.ACAD_SUB_PLAN,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ) ACAD_SUB_PLAN
– pick the first deliminated substring using the great REGEXP_SUBSTR function
,REGEXP_SUBSTR((LISTAGG(A.STDNT_CAR_NBR,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) STDNT_CAR_NBR1 ,REGEXP_SUBSTR((LISTAGG(A.acad_prog,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) ACAD_PROG1 ,REGEXP_SUBSTR((LISTAGG(b.ACAD_PLAN,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) ACAD_PLAN1 ,REGEXP_SUBSTR((LISTAGG(b.PLAN_SEQUENCE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) PLAN_SEQUENCE1 ,REGEXP_SUBSTR((LISTAGG(c.ACAD_PLAN_TYPE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) ACAD_PLAN_TYPE1 ,REGEXP_SUBSTR((LISTAGG(A.prog_status,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) PROG_STATUS1 ,REGEXP_SUBSTR((LISTAGG(C.DEGREE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) degree1 ,REGEXP_SUBSTR((LISTAGG(D.ACAD_SUB_PLAN,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 1) acad_sub_plan1
– pick the second deliminated substring (keep doing this for rest of column groups)
,REGEXP_SUBSTR((LISTAGG(A.STDNT_CAR_NBR,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) STDNT_CAR_NBR2 ,REGEXP_SUBSTR((LISTAGG(A.acad_prog,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) ACAD_PROG2 ,REGEXP_SUBSTR((LISTAGG(b.ACAD_PLAN,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) ACAD_PLAN2 ,REGEXP_SUBSTR((LISTAGG(b.PLAN_SEQUENCE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) PLAN_SEQUENCE2 ,REGEXP_SUBSTR((LISTAGG(c.ACAD_PLAN_TYPE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) ACAD_PLAN_TYPE2 ,REGEXP_SUBSTR((LISTAGG(A.prog_status,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) PROG_STATUS2 ,REGEXP_SUBSTR((LISTAGG(C.DEGREE,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) degree2 ,REGEXP_SUBSTR((LISTAGG(D.ACAD_SUB_PLAN,'/') WITHIN GROUP (ORDER BY A.STDNT_CAR_NBR,A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, C.ACAD_PLAN_TYPE, A.PROG_STATUS, C.DEGREE, D.ACAD_SUB_PLAN) ), '[^/]+', 1, 2) acad_sub_plan2 FROM PS_ACAD_PROG A , PS_ACAD_PLAN B , PS_ACAD_PLAN_TBL C , PS_ACAD_SUBPLAN D WHERE A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND B.EMPLID = D.EMPLID(+) AND B.ACAD_CAREER = D.ACAD_CAREER(+) AND B.STDNT_CAR_NBR = D.STDNT_CAR_NBR(+) AND B.ACAD_PLAN = D.ACAD_PLAN(+) AND B.EFFDT = D.EFFDT(+) AND B.EFFSEQ = D.EFFSEQ(+) AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.ACAD_CAREER = A_ED.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR AND A_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT) AND C.INSTITUTION= A.INSTITUTION AND C.ACAD_PLAN = B.ACAD_PLAN AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_ACAD_PLAN_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.ACAD_CAREER = C.ACAD_CAREER AND C1.ACAD_PLAN = C.ACAD_PLAN AND C1.EFFDT <= SYSDATE) GROUP BY A.EMPLID , A.INSTITUTION , A.ACAD_CAREER