Converting MSSQL 'FOR XML PATH' To Oracle

I have a Statement to fill a table on my MSSQL Database. It joins some values together seperated by a semicolon. I don't have enough Knowledge to convert this to Oracle. It should give me the same Output as from MSSQL. Can someone help me?

I have a Statement to fill a table on my MSSQL Database. It joins some values together seperated by a semicolon.

INSERT INTO XXAArcDocSWSB (ArcDocINr, SWorte) SELECT A.ArcDocINr, B.SWorte FROM XXAArcDoc A LEFT JOIN ( SELECT DISTINCT T2.ArcDocINr, SUBSTRING( ( SELECT ';' + T1.SWort AS [text()] FROM (SELECT D.ArcDocINr, SW.SWort FROM XXAArcDoc D, XXAArcSW SW WHERE D.ArcDocINr = SW.ArcDocINr) T1 WHERE T1.ArcDocINr = T2.ArcDocINr For XML PATH ('') ), 2, 255) [SWorte] FROM (SELECT D.ArcDocINr, SW.SWort FROM XXAArcDoc D, XXAArcSW SW WHERE D.ArcDocINr = SW.ArcDocINr) T2 ) B ON A.ArcDocINr = B.ArcDocINr 

I don't have enough Knowledge to convert this to Oracle. It should give me the same Output as from MSSQL. Can someone help me?

EDIT:

Here is some sample data:

XXAArcDoc:

ArcDocINr | ... ----------|---------- 1 | 2 | 3 | . | . | . | 

XXAArcSW:

ArcSWINr | ArcDocINr | SWort ---------|-----------|--------- 6 | 1 | Müller 7 | 1 | 100 8 | 2 | 111111 9 | 2 | 13579 10 | 2 | 002 11 | 3 | TM-AH 

And here is my desired Output:

ArcDocINr | SWorte ----------|--------- 1 | Müller;100 2 | 111111;13579;002 3 | TM-AH 
3

1 Answer

Use LISTAGG:

SELECT ArcDocINr, LISTAGG( SWort, ';' ) WITHIN GROUP ( ORDER BY ArcSWINr ) AS SWorte FROM XXAArcSW GROUP BY ArcDocINr; 

Update:

If you are inserting into the XXAArcDoc table using values from the XXAArcSW table then something like:

INSERT INTO XXAArcDoc ( ArcDocINr, SWorte ) SELECT ArcDocINr, LISTAGG( SWort, ';' ) WITHIN GROUP ( ORDER BY ArcSWINr ) FROM XXAArcSW GROUP BY ArcDocINr 
2

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoa21xZGaAcYCOnKanrpWnwaq6xmakrKuhoXqnu9Fmr6akXaWutbSMraZmp6KWsK2x

 Share!