CODEWITHSUNDEEP
sqloracle-databasepivotlistagg
Ivan_M2
Ivan_M2

Reputation: 37

Using Oracle SQL, how can I concatenate data from multiple rows into one row without using PIVOT, LISTAGG or custom built functions?

I'm using Oracle SQL. This is the table:

doc-nr code text
0000010 155 Sample text from code 155
0000010 455 Sample text1 from code 455
0000010 455 Sample text2 from code 455
0000010 455 Sample text3 from code 455

With this query:

SELECT t155.text as "155", t455.text as "455" 
FROM myTable t155
JOIN myTable t455 ON t155.doc-nr = t455.doc-nr
WHERE t155.code = '155'
AND t455.code = '455'

I get ugly result:

155 455
Sample text from code 155 Sample text1 from code 455
Sample text from code 155 Sample text2 from code 455
Sample text from code 155 Sample text3 from code 455

I want the output to be one row:

155 455 455 455
Sample text from code 155 Sample text1 from code 455 Sample text2 from code 455 Sample text3 from code 455

Or:

155 455
Sample text from code 155 Sample text1 from code 455; Sample text2 from code 455; Sample text3 from code 455

How can you do this with Oracle SQL without using PIVOT or LISTAGG or using custom built functions?

Upvotes: 1

Views: 240

Answers (3)

Sergey Afinogenov
Sergey Afinogenov

Reputation: 2212

Using xml functions in a such way is not good for performance (when there is more data) but in this case it works fine:

SELECT t155.text  as "155",
       sys_xmlagg(xmlelement(col, t455.text || ',')).extract('/ROWSET/COL/text()')
                 .getclobval() as "455"
  FROM myTable t155
  JOIN myTable t455
    ON t155."doc-nr" = t455."doc-nr"
 WHERE t155.code = '155'
   AND t455.code = '455'
 GROUP BY t155.text

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

You can create such a function returning SYS_REFCURSOR in order to pivot your data dynamically

CREATE OR REPLACE FUNCTION Get_Pivoted_Cols RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols1     VARCHAR2(32767);
  v_cols2     VARCHAR2(32767);  
BEGIN
  SELECT LISTAGG( ''''||rn||''' AS "'||rn||'"' , ',' ) WITHIN GROUP ( ORDER BY text ),
         LISTAGG( 'MAX("'||rn||'") AS "'||rn||'"' , ',' ) WITHIN GROUP ( ORDER BY text )
    INTO v_cols1, v_cols2
    FROM 
    ( SELECT code||'_'||ROW_NUMBER() OVER (PARTITION BY doc_nr, code ORDER BY text) AS rn, t.*
        FROM tab t );

  v_sql :='SELECT '||v_cols2||
          '  FROM
             (
              SELECT code||''_''||ROW_NUMBER() OVER (PARTITION BY doc_nr, code ORDER BY text) AS rn, t.*
                FROM tab t 
             )
            PIVOT
            (
             MAX(text) FOR rn IN ( '|| v_cols1 ||' )
            )
            GROUP BY doc_nr';            

  OPEN v_recordset FOR v_sql;
  DBMS_OUTPUT.PUT_LINE(v_sql);
  RETURN v_recordset;
END;

which will generate this output as a SQL statement

SELECT MAX("155_1") AS "155_1", 
       MAX("455_1") AS "455_1", 
       MAX("455_2") AS "455_2", 
       MAX("455_3") AS "455_3"
  FROM
  (
   SELECT code||'_'||ROW_NUMBER() OVER (PARTITION BY doc_nr, code ORDER BY text) AS rn, 
          t.*
     FROM tab t 
  )
  PIVOT
  (
   MAX(text) FOR rn IN ( '155_1' AS "155_1", '455_1' AS "455_1", '455_2' AS "455_2", 
                         '455_3' AS "455_3" )
  )
  GROUP BY doc_nr;

Demo

and then run this code :

VAR rc REFCURSOR
EXEC :rc := Get_Pivoted_Cols;
PRINT rc

from SQL Developer's Command Line in order to see the result set.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191285

If the problem is the size, you can use XML aggregation:

select mt.doc_nr, mt.text as text_155,
  xmlquery('
    for $i in .
      return <e>{ fn:string-join($i/text, "; ") }</e>/text()'
    passing tmp.xml
    returning content
  ).getclobval() as text_455
from mytable mt
join (
  select doc_nr, xmlagg(xmlelement("text", text)) as xml
  from mytable
  where code = 455
  group by doc_nr
) tmp
on tmp.doc_nr = mt.doc_nr
where mt.code = 155

DOC_NR  | TEXT_155                  | TEXT_455                                                                          
:------ | :------------------------ | :---------------------------------------------------------------------------------
0000010 | Sample text from code 155 | Sample text1 from code 455; Sample text3 from code 455; Sample text2 from code 455

db<>fiddle including the intermediate steps.

Upvotes: 1

Related Questions