| Listagg over Listagg [message #684213] |
Sat, 24 April 2021 01:46  |
 |
abhayman
Messages: 37
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
physics;PROF.TMP
bio;EMPLOYEES
I want the output in below format
physics;PROF.TMP,bio;EMPLOYEES
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  |
 |
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Fri, 08 January 2021 12:29Michel 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.
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
