13 Replies Latest reply: Nov 6, 2012 6:33 AM by AlbertoFaenza RSS

    Concat rows values into single column

    dbdan
      Hi All,

      How can I concat values of a multiple rows into a single column by separating them by comma.
      Eg:

      SELECT empno FROM emp;
      empno
      1
      2
      3
      4

      I want output should be:
      Empnos
      1,2,3,4

      Thanks & Regards,
      Danish
        • 1. Re: Concat rows values into single column
          jortri
          For example
          WITH t AS 
            (SELECT 1 AS col FROM dual
            union
            SELECT 2 AS col FROM dual 
            union
            SELECT 3 AS col FROM dual 
            union
            SELECT 4 AS col FROM dual)
          SELECT SUBSTR(MAX(col),2) CADENA
          FROM (SELECT SYS_CONNECT_BY_PATH(col, ',') col
                FROM ( SELECT col,
                              ROW_NUMBER() OVER (ORDER BY col) FILA
                        FROM t)
                START WITH FILA = 1
                CONNECT BY PRIOR FILA = FILA - 1 )
          /
          
          CADENA
          ----------
          1,2,3,4
          • 2. Re: Concat rows values into single column
            Frank Kulash
            Hi, Danish,

            That's called "String Aggregation"
            [AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] shows several different ways to do it.

            I recommend the first one, the user-defined function STRAGG, which you can copy from that page.

            Once you have STRAGG installed, your query is simply
            SELECT    STRAGG (empno)     AS empnos
            FROM      emp;
            On Oracle 10 (and up) you may have a similar function, WM_CONCAT (owned by WMSYS), already installed.
            WM_CONCAT is not documented, so you may not want to use it in your Production applications.

            STRAGG is not so convenient if the order of items in the concatenated string is important.
            In that case, use XMLAGG or SYS_CONNECT_BY_PATH, as shown later in the asktom page.
            MODEL can also do ordered string aggregation.
            • 3. Re: Concat rows values into single column
              BluShadow
              More correctly written (for 10g and above)..
              SQL> ed
              Wrote file afiedt.buf
              
                1  WITH t AS
                2    (SELECT 1 AS col FROM dual
                3    union
                4    SELECT 2 AS col FROM dual
                5    union
                6    SELECT 3 AS col FROM dual
                7    union
                8    SELECT 4 AS col FROM dual)
                9  --
               10  SELECT LTRIM(SYS_CONNECT_BY_PATH(col, ','),',') col
               11  FROM ( SELECT col,
               12                ROW_NUMBER() OVER (ORDER BY col) FILA
               13         FROM t)
               14  WHERE CONNECT_BY_ISLEAF = 1
               15  START WITH FILA = 1
               16* CONNECT BY PRIOR FILA = FILA - 1
              SQL> /
              
              COL
              -----------------------------------------------------------
              1,2,3,4
              
              SQL>
              using CONNECT_BY_ISLEAF = 1 to pick out the leaf reaching paths rather than incorrectly using the max function.
              • 4. Re: Concat rows values into single column
                jortri
                True.
                I worked now in 9.
                • 5. Re: Concat rows values into single column
                  dbdan
                  Thanks Jortri and Blueshadow....

                  It works.... I need to understand it now :)

                  Regards,
                  Danish
                  • 6. Re: Concat rows values into single column
                    MichaelS
                    Or
                    SQL>  select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()').extract('//text()') ,',') empnos from emp
                    /
                    EMPNOS                                                                          
                    --------------------------------------------------------------------------------
                    7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934  
                    • 7. Re: Concat rows values into single column
                      dbdan
                      Even this is simple.

                      Thanks,
                      Danish
                      • 8. Re: Concat rows values into single column
                        890499
                        @Micheals .....Thanks alot . Yours was the best answer .
                        • 9. Re: Concat rows values into single column
                          946001
                          Michael's Thanks a lot.
                          It works for me.

                          Rgds--
                          Adesh
                          • 10. Re: Concat rows values into single column
                            972776
                            hey micheal it works bt when i am using it in oracle report 6i then it gives error what should i do..:(
                            • 11. Re: Concat rows values into single column
                              AlbertoFaenza
                              MichaelS wrote:
                              Or
                              SQL>  select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()').extract('//text()') ,',') empnos from emp
                              /
                              EMPNOS                                                                          
                              --------------------------------------------------------------------------------
                              7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934  
                              Hi Michael,

                              is it an error or is it correct to put extract 2 times? This is giving to me the same result:
                              select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()'),',') empnos from emp;
                              EMPNOS                                                                          
                              --------------------------------------------------------------------------------
                              7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934           
                              Regards.
                              Al
                              • 12. Re: Concat rows values into single column
                                jeneesh
                                Wrongly replied..

                                Edited by: jeneesh on Nov 6, 2012 5:41 PM

                                FYI: It is a very old thread...
                                • 13. Re: Concat rows values into single column
                                  AlbertoFaenza
                                  Hi Jeneesh,

                                  thx. I did not notice that it was an old thread and that 969773 has hijacked it.

                                  Regards.
                                  Al