Home » SQL & PL/SQL » SQL & PL/SQL » Listagg over Listagg (Oracle 12g)
Listagg over Listagg [message #684213] Sat, 24 April 2021 01:46 Go to next message
abhayman
Messages: 35
Registered: August 2011
Location: CA
Member
Hi,

I wanted to get some data from multiple rows under same column (SRAV.XYZ) and concat it with other col hence used the listagg query.

SELECT LISTAGG (
REGEXP_SUBSTR (SRAV.XYZ,
'[^:]+$'),
';')
WITHIN GROUP (ORDER BY
REGEXP_SUBSTR (
SRAV.XYZ,
'[^:]+$')) ||';'||SRA.ABC
/*(CASE
WHEN SRA.ABC like 'PROF.TMP' THEN SRA.ABC = 'TMP'
WHEN SRA.ABC like 'PROF' THEN SRA.ABC ='PROF'
ELSE SRA.ABC='EMPLOYEES' END) */
FROM TEST1 SPAEM,
TEST2 SRAV,
TEST3  srm,
TEST4  SRA
WHERE SRAV.RID = srm.RGID
AND SRAV.PID IN
('123RTU23',
'456U43',
'AB4577Y')
AND SRAV.XYZ IS NOT NULL
AND SPAEM.EMPID = srm.SEC_UUID
AND SRAV.PID = SRA.PRID
AND SPAEM.EMPID = 139806
group by ABC
I am able to get the output in below format.

physics;PROF.TMP
bio;EMPLOYEES
Now, I am having issue which I am unable to handle.

I want the output in below format
physics;PROF.TMP,bio;EMPLOYEES
Any help on this .

Regards.

[Updated on: Sat, 24 April 2021 02:29]

Report message to a moderator

Re: Listagg over Listagg [message #684214 is a reply to message #684213] Sat, 24 April 2021 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67882
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Fri, 08 January 2021 12:29
Michel Cadot wrote on Fri, 08 January 2021 12:08

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
And many are still waiting for your feedback in your previous topics.

Re: Listagg over Listagg [message #684215 is a reply to message #684213] Sat, 24 April 2021 03:03 Go to previous message
abhayman
Messages: 35
Registered: August 2011
Location: CA
Member
Fixed.

https://stackoverflow.com/questions/67240162/listagg-over-listagg-oracle/67240231#67240231
Previous Topic: upper case in create table
Next Topic: grouping logic
Goto Forum:
  


Current Time: Tue Jun 15 16:18:32 CDT 2021