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
31 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
2ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoa21xZGaAcYCOnKanrpWnwaq6xmakrKuhoXqnu9Fmr6akXaWutbSMraZmp6KWsK2x