Flatten multiple records into Single concatenated 2

[IanWaterman]

I am trying to list a number of insurance policy endorsements into a single row.

Policy Version Endt
Pol1 V1 Endt1
Pol1 V1 Endt2

Need
Pol1 V1 Endt1 | Endt2

There can be any number of Endts though likely to be less than 10
Need to Group by Policy No and Version and delimit Endts with spaces and pipes " | "

Thank you

Ian

 

[sxschech]

If you are using version 10, try XMLAgg:
Example:

SELECT POLICY, VERSION,
     , RTRIM
       ( xmlagg (xmlelement (c, Endt || '|') order by POLICY, VERSION, Endt).extract ('//text()')
       , '|' ) AS concatenated
FROM   test
GROUP BY POLICY, VERSION;

If you are using version 11, try ListAgg:
Example:

SELECT POLICY, VERSION,
RTRIM (
       XMLAGG (XMLELEMENT (c, Endt|| '|') ORDER BY
                              Endt).EXTRACT ('//text()'),
                                    '|')
                                    AS Endt
From test
GROUP BY POLICY, VERSION;

 

[SantaMufasa]

Here is an alternative that may simplify things for you:

select * from ian;

POLICY VERSION ENDT
------ ------- -----
Pol1   V1      Endt1
Pol1   V1      Endt2
Pol1   V1      Endt3
Pol1   V1      Endt4
Pol2   V1      Endt1
Pol2   V1      Endt2
Pol3   V1      Endt3
Pol3   V1      Endt4

8 rows selected.

select policy,version,replace(wm_concat(endt),',',' | ') endt
  from ian
 group by policy,version
/

POLICY VERSION ENDT
------ ------- -----------------------------
Pol1   V1      Endt1 | Endt2 | Endt3 | Endt4
Pol2   V1      Endt1 | Endt2
Pol3   V1      Endt3 | Endt4

3 rows selected.

Let us know if this meets your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sxschech]

Hi Santa,

I heard about wm_concat, but was told that it was undocumented and wasn't recommended to use, so that is why didn't suggest it. I do agree it is simpler to set up and use though.

 

[IanWaterman]

Dave

Solution works brilliantly.

Thank you

Ian

 

[IanWaterman]

Dave & Sxchech

Can I be cheeky and ask you to comment on my post about WITH

Everything I have read leads me to believe it will speed up my query I just cant work out how to use it in my query

Thank you

Ian

 

[sxschech]

Looks like I did a bad copy and paste for the version 11 listagg example. It should be:

SELECT POLICY, VERSION,
              listagg(endt,'|') within group (order by Policy, Version) as concatenated
                            FROM    test    
                            GROUP BY Policy, Version;