Converting MSSQL 'FOR XML PATH' To Oracle

Question

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

I recommend you show some sample data and, more importantly, your desired output. — Jeff Holt, Jul 06 '17 at 06:32

MT0 · Accepted Answer · 2017-07-06T08:51:28.587

4

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

edited Jul 06 '17 at 08:51

answered Jul 06 '17 at 08:04

MT0

143,790
11
59
117

This gives me an error: Unknown Field [LISTAGG(SWort, ';')] for table XXAArcSW. But if I reorder ArcDocINr and LISTAGG, it works. Can you give me an example for placing this into a Insert-Statement? – F. Baum Jul 06 '17 at 08:35
@F.Baum Example added. – MT0 Jul 06 '17 at 08:51

Converting MSSQL 'FOR XML PATH' To Oracle

1 Answers1