1 2 3 Previous Next 30 Replies Latest reply: Oct 6, 2013 5:28 AM by BrendanP RSS

    Multiple Rows Into One Column Field

    shagar mahabubjan

      Hi All,

       

             Today I tried one query:

      select wm_concat(ename) from emp

      group by deptno;

       

      I have a data that looks like this.


      CLARK,KING,MILLER,SREE

      JONES,FORD,ADAMS,SCOTT

      ALLEN,MARTIN,BLAKE,TURNER,JAMES,WARD

       

      Can someone help me to build an SQL command that would have the output as follows:

      I need per column 3 values....


      CLARK,KING,MILLER,

      SREE,JONES,FORD,

      ADAMS,SCOTT,ALLEN,

      MARTIN,BLAKE,TURNER,

      JAMES,WARD

        • 1. Re: Multiple Rows Into One Column Field
          Manik

          If you are on 11g... you can try as below

          -----------------------------

          WITH t AS

                  (SELECT 'CLARK,KING,MILLER,SREE' str FROM DUAL

                   UNION ALL

                   SELECT 'JONES,FORD,ADAMS,SCOTT' FROM DUAL

                   UNION ALL

                   SELECT 'ALLEN,MARTIN,BLAKE,TURNER,JAMES,WARD' FROM DUAL),

               tt AS

                  (    SELECT r,

                              CEIL (ROWNUM / 3) rn,

                              REGEXP_SUBSTR (str,

                                             '[^,]+',

                                             1,

                                             LEVEL)

                                 AS WORD

                         FROM (SELECT ROWNUM r, str FROM t) t

                   CONNECT BY     LEVEL <= REGEXP_COUNT (str, ',') + 1

                              AND PRIOR t.r = t.r

                              AND PRIOR SYS_GUID () IS NOT NULL)

          SELECT listagg(word,',') within group (order by r) str

            FROM tt

            group by rn;

           

          ----------------------

          Output:

           

          STR

          ------------

          CLARK,KING,MILLER

          SREE,FORD,JONES

          ADAMS,SCOTT,ALLEN

          BLAKE,MARTIN,TURNER

          JAMES,WARD

           

          Cheers,

          Manik.

          • 2. Re: Multiple Rows Into One Column Field
            shagar mahabubjan

            Hi Manik thanks for your reply

             

                   I am using 10g and This query is getting very difficult for me can u please send me a simple query....

            • 3. Re: Multiple Rows Into One Column Field
              Manik

              I am really sorry for that if that seems difficult interms of understanding..

               

              My algorithm remains simple as it does the following steps:

               

              1) Spilt all the rows based on coma as separator

              2) Take help of rownum to distinguish your rows, can be also used as ordering entity for aggregation in later step.

              3) ceil(rownum/3) would try to assign 111 222 333 etc.... so that this way you have a method to group them in next step.

              4) Aggregate the strings based on step 3..

               

              Try to execute the queries bit by bit and step by step .. check output at each step, you would understand..

               

              There might be simpler ways to do this (which I do not know ) May be you can wait on the replies from others on this...

               

              Cheers,

              Manik.

              • 4. Re: Multiple Rows Into One Column Field
                Manik

                Also for 10g you may use this:

                --------------------------------

                 

                WITH t AS

                        (SELECT 'CLARK,KING,MILLER,SREE' str FROM DUAL

                         UNION ALL

                         SELECT 'JONES,FORD,ADAMS,SCOTT' FROM DUAL

                         UNION ALL

                         SELECT 'ALLEN,MARTIN,BLAKE,TURNER,JAMES,WARD' FROM DUAL),

                     tt AS

                        (    SELECT r,

                                    CEIL (ROWNUM / 3) rn,

                                    REGEXP_SUBSTR (str,

                                                   '[^,]+',

                                                   1,

                                                   LEVEL)

                                       AS WORD

                               FROM (SELECT ROWNUM r, str FROM t) t

                         CONNECT BY     LEVEL <= REGEXP_COUNT (str, ',') + 1

                                    AND PRIOR t.r = t.r

                                    AND PRIOR SYS_GUID () IS NOT NULL)

                  SELECT                  -- listagg (word, ',') WITHIN GROUP (ORDER BY r) str  ------- commented

                        RTRIM (

                            XMLAGG (XMLELEMENT (tt, word || ',')).EXTRACT ('//text()').EXTRACT (

                               '//text()'),

                            ',')

                            str

                    FROM tt

                GROUP BY rn;

                 

                Output:

                -------

                 

                STR

                ----------------

                CLARK,MILLER,KING

                SREE,FORD,JONES

                ADAMS,ALLEN,SCOTT

                MARTIN,TURNER,BLAKE

                JAMES,WARD

                ---------------------------------------------

                 

                Cheers,

                Manik.

                • 5. Re: Multiple Rows Into One Column Field
                  BluShadow

                  shagarmahabubjan wrote:

                   

                  Hi All,

                   

                         Today I tried one query:

                  select wm_concat(ename) from emp

                  group by deptno;

                   

                  I have a data that looks like this.


                  CLARK,KING,MILLER,SREE

                  JONES,FORD,ADAMS,SCOTT

                  ALLEN,MARTIN,BLAKE,TURNER,JAMES,WARD

                   

                  Can someone help me to build an SQL command that would have the output as follows:

                  I need per column 3 values....


                  CLARK,KING,MILLER,

                  SREE,JONES,FORD,

                  ADAMS,SCOTT,ALLEN,

                  MARTIN,BLAKE,TURNER,

                  JAMES,WARD

                   

                  You shouldn't use WM_CONCAT.  It's an undocumented function that may change or be removed in future versions of the database and you'll have no comeback on Oracle if it does change and your code no longer works.

                  Oracle says not to use undocumented functionality.  Tom Kyte says not to use undocumented functionality.  All Oracle experts say not to use undocumented functionality, so I think you probably get the idea that you shouldn't. 

                  • 6. Re: Multiple Rows Into One Column Field
                    tkyte

                    see

                     

                    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7185307800346182830

                     

                    ops$tkyte%ORA11GR2> select rtrim(
                      2         max( decode( mod(rownum-1,3), 0, ename ))||','||
                      3         max( decode( mod(rownum-1,3), 1, ename ))||','||
                      4         max( decode( mod(rownum-1,3), 2, ename )), ',' )
                      5    from scott.emp
                      6   group by floor( rownum/3-.1)
                      7   order by floor( rownum/3-.1)
                      8  /
                    
                    RTRIM(MAX(DECODE(MOD(ROWNUM-1,3)
                    --------------------------------
                    SMITH,ALLEN,WARD
                    JONES,MARTIN,BLAKE
                    CLARK,SCOTT,KING
                    TURNER,ADAMS,JAMES
                    FORD,MILLER
                    
                    • 7. Re: Multiple Rows Into One Column Field
                      Manik

                      Wow!! was that Tom Kyte   Awesome!!!!!!!!!

                       

                      Cheers,

                      Manik.

                      • 8. Re: Multiple Rows Into One Column Field
                        BluShadow

                        Manik wrote:

                         

                        Wow!! was that Tom Kyte   Awesome!!!!!!!!!

                         

                        Cheers,

                        Manik.

                         

                        LOL! Yes, Tom does pop up here occasionally.... probably when we mention his name....  (Tom are you one of those people who searches the web for their name?  Come on, admit it!  )

                        • 9. Re: Multiple Rows Into One Column Field
                          user7347338

                          Blushadow,

                           

                          Click on the link Tom provided: someone referred this thread to Tom and asked him for a solution. Tom answered the question, then repeated the answer here for the forum's benefit. He didn't need to search anything.

                           

                          He has stated that he "lurks" on the OTN forums and elsewhere, so I try to watch my step

                          • 10. Re: Multiple Rows Into One Column Field
                            BluShadow

                            Yes, I was joking with Tom. 

                            • 11. Re: Multiple Rows Into One Column Field
                              Stew Ashton

                              That's funny, I don't get the same answer:

                               

                              select rtrim(
                                     max( decode( mod(rownum-1,3), 0, ename ))||','||
                                     max( decode( mod(rownum-1,3), 1, ename ))||','||
                                     max( decode( mod(rownum-1,3), 2, ename )), ',' ) enames
                                from emp
                              group by floor( rownum/3-.1)
                              order by floor( rownum/3-.1)
                              /
                              

                               

                              ENAMES
                              WARD,TURNER,SMITH
                              SCOTT,MILLER,MARTIN
                              KING,JONES,JAMES
                              FORD,CLARK,BLAKE
                              ALLEN,ADAMS
                              • 12. Re: Multiple Rows Into One Column Field
                                Etbin

                                StewAshton wrote:

                                That's funny, I don't get the same answer

                                 

                                Why do you think you should?

                                Besides, you're not the only one.

                                 

                                ENAMES
                                KING,BLAKE,CLARK
                                JONES,SCOTT,FORD
                                SMITH,ALLEN,WARD
                                MARTIN,TURNER,ADAMS
                                JAMES,MILLER

                                 

                                Regards

                                 

                                Etbin


                                 

                                BANNER
                                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                                PL/SQL Release 11.2.0.3.0 - Production
                                CORE 11.2.0.3.0 Production
                                TNS for Linux: Version 11.2.0.3.0 - Production
                                NLSRTL Version 11.2.0.3.0 - Production


                                It's from my APEX Workspace: first select from the emp table (not touched until now)

                                 

                                Message was edited by: Etbin

                                • 13. Re: Multiple Rows Into One Column Field
                                  Hoek

                                  user7347338 wrote:

                                   

                                  Blushadow,

                                   

                                  Click on the link Tom provided: someone referred this thread to Tom [..]

                                   

                                  I wonder who did that....

                                  Obviously I was wrong thinking connect by would do it (well, sofar, that is, I'll struggle on with it when I've got some time for it).

                                  But I'd never thought that the 'classic pivot approach' would get it done.

                                  • 14. Re: Multiple Rows Into One Column Field
                                    Hoek

                                    Database versions, people!

                                     

                                    Here's what I got on 11.2.0.3:

                                     

                                    SQL> select rtrim( max( decode( mod(rownum-1,3), 0, ename ))||','||
                                      2                max( decode( mod(rownum-1,3), 1, ename ))||','||
                                      3                max( decode( mod(rownum-1,3), 2, ename ))
                                      4              , ','
                                      5              )
                                      6  from   emp
                                      7  group by floor( rownum/3-.1)
                                      8  order by floor( rownum/3-.1);

                                    RTRIM(MAX(DECODE(MOD(ROWNUM-1,3)
                                    --------------------------------
                                    SMITH,ALLEN,WARD
                                    JONES,MARTIN,BLAKE
                                    CLARK,SCOTT,KING
                                    TURNER,ADAMS,JAMES
                                    FORD,MILLER

                                    5 rows selected.

                                    SQL> select banner from v$version;

                                    BANNER
                                    --------------------------------------------------------------------------------
                                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                                    1 2 3 Previous Next