3 Replies Latest reply: Jan 6, 2011 8:44 AM by 737905 RSS

    how to concatenate multiple rows into one row using a SQL query

    579729
      Hi
      I have a table table1 and three columns col1

      data

      col1
      c11
      c12
      c13
      c14
      c15

      Now using a SQL query (not any PL/SQL) I want the following output.

      c11-c12-c13-c14-c15.

      Is it possible?
      If possible then please tell me how can I do that?

      Regards
        • 1. Re: how to concatenate multiple rows into one row using a SQL query
          Hoek
          Several techniques, database version depenent:

          http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
          • 2. Re: how to concatenate multiple rows into one row using a SQL query
            Frank Kulash
            Hi,

            That's called String Aggregation , and this page shows several ways to do it:
            http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

            Which one should you uise? That depends on your version of Oracle, and your exact requirements, especially whether the order is important. Whenever you have a problem, say what version of Oracle you're using, and describe how you get the results you want.
            Overall, LISTAGG is probably the best way to do string aggregation.
            • 3. Re: how to concatenate multiple rows into one row using a SQL query
              737905
              Try this:
              with t as (SELECT 'c11' col1 from dual
              union select 'c12' from dual
              union select 'c13' from dual
              union select 'c14' from dual
              union select 'c15' from dual)
              SELECT LTRIM(col,'-') output FROM
              (SELECT MAX(SYS_CONNECT_BY_PATH(col1,'-')) KEEP(DENSE_RANK LAST ORDER BY col1) col
              FROM (SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) curr,
                                 ROW_NUMBER() OVER(ORDER BY col1) -1 prev
                    FROM t)
              CONNECT BY PRIOR curr = prev  
              START WITH curr = 1)
              /
              
              output
              ----------
              c11-c12-c13-c14-c15
              
              -------------------------------------------------------------------
              
              SQL> with t as (SELECT 'c11' col1 from dual
              union select 'c12' from dual
              union select 'c13' from dual
              union select 'c14' from dual
              union select 'c15' from dual)
              SELECT RTRIM(XMLAGG(XMLELEMENT(e,col1||'-')).EXTRACT('//text()'),'-') output FROM t
              /
              
              output
              ----------
              c11-c12-c13-c14-c15
              
              SQL>