1 2 3 Previous Next 30 Replies Latest reply: Oct 6, 2013 5:28 AM by BrendanP Go to original post RSS
      • 15. Re: Multiple Rows Into One Column Field
        Etbin

        Sorry, I thought everybody would have recognized it by the shape of the result.

        Updated my post above.

         

        Regards

         

        Etbin

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

          Connect by version:

           

          select ltrim(sys_connect_by_path(ename,','),',') enames
          from (
            select ename,
            mod(row_number() over(order by ename)-1,3) modd,
            ceil(row_number() over(order by ename)/3) grp
            from scott.emp
          )
          where connect_by_isleaf = 1
          start with modd = 0
          connect by (grp, modd) = ((prior grp, prior modd + 1));
          

           

          11GR2+ version:

           

          select listagg(ename,',') within group(order by ename) enames
          from (
            select ename,
            ceil(row_number() over(order by ename)/3) grp
            from scott.emp
          )
          group by grp
          order by grp;
          
          • 18. Re: Multiple Rows Into One Column Field
            BluShadow

            Hoek wrote:

             

            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.

             

            Yes, as it's a fixed 3 required, Tom's approach makes sense as it's simpler to just concatenate the 3 together than to use connect by aggregation.

             

            It's perfectly possible though e.g. (a version that works in 10g upwards)...

             

             

            SQL> ed
            Wrote file afiedt.buf

              1  select deptno
              2        ,ltrim(sys_connect_by_path(ename,','),',') as enames
              3  from (
              4        select deptno, ename
              5              ,floor((row_number() over (partition by deptno order by empno)-1)/3) as grp
              6              ,mod(row_number() over (partition by deptno order by empno)-1,3) as rn
              7        from   emp
              8       )
              9  where connect_by_isleaf = 1
            10  connect by deptno = prior deptno
            11          and grp = prior grp
            12          and rn = prior rn+1
            13* start with rn = 0
            SQL> /

             

                DEPTNO ENAMES
            ---------- ------------------------------
                    10 CLARK,KING,MILLER
                    20 SMITH,JONES,SCOTT
                    20 ADAMS,FORD
                    30 ALLEN,WARD,MARTIN
                    30 BLAKE,TURNER,JAMES

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

              Ooo, I see Stew has already provided a version of connect by... (I should have looked on page 2 of the discussion hehe!).

               

              Similar principle, creating a group and row number within each department.  Stew's is a little more concise.

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

                Exactly what I was after! (including deptno)

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

                  Stew - sure you did!

                   

                  without an order by - both answers are the "same"

                   

                  if you wanted a predicable order (not part of the original spec) replace rownum:

                   

                  row_number() over (order by WHATEVER-but-make-sure-it-is-unique-to-be-deterministic)

                   

                  (you'll need an inline view as well)

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

                    Missed it earlier today, but now I notice:

                    connect by (grp, modd) = ((prior grp, prior modd + 1))

                     

                    Stew, how on earth did you figure out that the double brackets (around the prior columns ) would make your connect by work?

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

                      It's just shorthand for grp = prior grp and modd = prior modd + 1

                       

                      When you compare compound expressions, you need an extra pair of parentheses on the right.

                       

                      If I had said (grp, modd) IN ( (prior grp, prior modd + 1), (prior grp, prior modd + 2) )

                      or (grp, modd) = ANY ( (prior grp, prior modd + 1), (prior grp, prior modd + 2) )

                       

                      it might have been less surprising, but it's the same syntax.

                       

                      See Comparison Conditions

                      • 24. Re: Multiple Rows Into One Column Field
                        Solomon Yakobson

                        And without group by:

                         

                        with t as (

                                   select  row_number() over(order by ename) rn,

                                           ename ||

                                           lead(','||ename) over(order by ename) ||

                                           lead(','||ename,2) over(order by ename) trio

                                     from  emp

                                  )

                        select  trio

                          from  t

                          where mod(rn,3) = 1

                          order by rn

                        /


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

                        SQL>

                         

                        SY.

                        • 25. Re: Multiple Rows Into One Column Field
                          BrendanP

                          What if your requirements are:

                          • Output is limited by line size, not number of names
                          • You want to practise 12c row patterns :)
                          NAME_LIST
                          --------------------------------------------------------------------------------
                          Abel, Ellen; Ande, Sundar; Atkinson, Mozhe; Austin, David; Baer, Hermann
                          Baida, Shelli; Banda, Amit; Bates, Elizabeth; Bell, Sarah; Bernstein, David
                          Bissot, Laura; Bloom, Harrison; Bull, Alexis; Cabrio, Anthony; Cambrault, Gerald
                          Cambrault, Nanette; Chen, John; Chung, Kelly; Colmenares, Karen; Davies, Curtis
                          De Haan, Lex; Dellinger, Julia; Dilly, Jennifer; Doran, Louise; Ernst, Bruce
                          Errazuriz, Alberto; Everett, Britney; Faviet, Daniel; Fay, Pat; Feeney, Kevin
                          Fleaur, Jean; Fox, Tayler; Fripp, Adam; Gates, Timothy; Gee, Ki; Geoni, Girard
                          Gietz, William; Grant, Douglas; Grant, Kimberely; Greenberg, Nancy
                          Greene, Danielle; Hall, Peter; Hartstein, Michael; Higgins, Shelley; Himuro, Guy
                          Hunold, Alexander; Hutton, Alyssa; Johnson, Charles; Jones, Vance
                          Kaufling, Payam; Khoo, Alexander; King, Janette; King, Steven; Kochhar, Neena
                          Kumar, Sundita; Ladwig, Renske; Landry, James; Lee, David; Livingston, Jack
                          Lorentz, Diana; Mallin, Jason; Markle, Steven; Marlow, James; Marvins, Mattea
                          Matos, Randall; Mavris, Susan; McCain, Samuel; McEwen, Allan; Mikkilineni, Irene
                          Mourgos, Kevin; Nayer, Julia; OConnell, Donald; Olsen, Christopher; Olson, TJ
                          Ozer, Lisa; Partners, Karen; Pataballa, Valli; Patel, Joshua; Perkins, Randall
                          Philtanker, Hazel; Popp, Luis; Rajs, Trenna; Raphaely, Den; Rogers, Michael
                          Russell, John; Sarchand, Nandita; Sciarra, Ismael; Seo, John; Sewall, Sarath
                          Smith, Lindsey; Smith, William; Stiles, Stephen; Sullivan, Martha
                          Sully, Patrick; Taylor, Jonathon; Taylor, Winston; Tobias, Sigal; Tucker, Peter
                          Tuvault, Oliver; Urman, Jose Manuel; Vargas, Peter; Vishney, Clara
                          Vollman, Shanta; Walsh, Alana; Weiss, Matthew; Whalen, Jennifer; Zlotkey, Eleni
                          
                          22 rows selected.
                          
                          Elapsed: 00:00:00.03
                            1  WITH emp_ordered AS (
                            2  SELECT Row_Number() OVER (ORDER BY last_name || ', ' || first_name) rn,
                            3         last_name || ', ' || first_name ename
                            4    FROM hr.employees e
                            5  ), emp_rsf (rn, name_list, new_line) AS (
                            6  SELECT rn, ename, 1
                            7    FROM emp_ordered
                            8   WHERE rn = 1
                            9  UNION ALL
                           10  SELECT e.rn,
                           11         CASE WHEN Length (r.name_list || '; ' || e.ename) > :rec_len THEN e.ename
                           12              ELSE r.name_list || '; ' || e.ename END,
                           13         CASE WHEN Length (r.name_list || '; ' || e.ename) > :rec_len THEN 1
                           14              ELSE 0 END
                           15    FROM emp_rsf r
                           16    JOIN emp_ordered e
                           17      ON e.rn = r.rn + 1
                           18  )
                           19  SELECT name_list
                           20    FROM emp_rsf
                           21   MATCH_RECOGNIZE (
                           22   ORDER BY rn
                           23   MEASURES name_list AS name_list,
                           24            new_line AS new_line
                           25   ONE ROW PER MATCH
                           26   PATTERN ( strt sm* )
                           27   DEFINE
                           28     sm AS sm.new_line = 0
                           29   )
                           30*  ORDER BY 1
                          • Doesn't work from SQL Developer which doesn't recognise 12c SQL - hoping for a watercooler moment between database and sqldev PMs some time soon :)
                          • In 11.2 you can use analytics instead of match_recognise
                          • 1n 10.2, am pretty sure connect by won't do it, but you can use MODEL - and in fact that is probably better (edit below of 13 Aug)

                           

                          Edit: Bug fix, Original pattern up+ fails if one name, need ( strt up* ); also, length change not foolproof

                           

                          Edit, 13 Aug: Realised that you can do this using MODEL in 10g, then realised that it is probably better with MODEL. Here is MODEL/10g version, with partitionning added:

                           

                          WITH mod AS (
                          SELECT *
                            FROM hr.employees
                          MODEL
                            PARTITION BY (department_id)
                            DIMENSION BY (Row_Number () OVER (PARTITION BY department_id ORDER BY last_name, first_name) rn)
                            MEASURES (
                              last_name || ', ' || first_name ename,
                              CAST (NULL AS VARCHAR2(4000)) ename_list,
                              0 line_print
                            )
                            RULES (
                              ename_list[ANY] = CASE WHEN ename_list[CV()-1] IS NULL OR Length (ename_list[CV()-1] ||  '; ' || ename[CV()]) > :rec_len THEN ename[CV()]
                                                     ELSE ename_list[CV()-1] ||  '; ' || ename[CV()]
                                                END,
                              line_print[ANY] = CASE WHEN ename[CV()+1] IS NULL OR Length (ename_list[CV()] ||  '; ' || ename[CV()+1]) > :rec_len THEN 1
                                                     ELSE 0
                                                END
                            )
                          )
                          SELECT department_id dept, ename_list
                            FROM mod
                           WHERE line_print = 1
                           ORDER BY department_id, ename_list
                          • 26. Re: Multiple Rows Into One Column Field
                            Solomon Yakobson

                            Nice example. Just one small note. OP wants 3 names per row while your code splits by row length. So I made a small adjustment:

                             

                            WITH emp_ordered AS (

                                                 SELECT  Row_Number() OVER(ORDER BY last_name || ', ' || first_name) rn,

                                                         last_name || ', ' || first_name ename

                                                   FROM  hr.employees e

                                                ),

                            emp_rsf (rn, name_list, len) AS (

                                                              SELECT  rn,

                                                                      ename,

                                                                      Length(ename)

                                                                FROM  emp_ordered

                                                                WHERE rn = 1

                                                             UNION ALL

                                                              SELECT  e.rn,

                                                                      CASE

                                                                        WHEN regexp_count(r.name_list,'; ') = :names_per_row - 1 THEN e.ename

                                                                        ELSE r.name_list || '; ' || e.ename

                                                                      END,

                                                                      Length(

                                                                             CASE

                                                                               WHEN regexp_count(r.name_list,'; ') = :names_per_row - 1 THEN e.ename

                                                                               ELSE r.name_list || '; ' || e.ename

                                                                             END

                                                                            )

                                                                FROM      emp_rsf r

                                                                      JOIN

                                                                          emp_ordered e

                                                                        ON e.rn = r.rn + 1

                                                            )

                            SELECT  name_list

                              FROM  emp_rsf

                              MATCH_RECOGNIZE(

                                              ORDER BY rn

                                              MEASURES up.name_list AS name_list,

                                                       up.len AS len

                                              ONE ROW PER MATCH

                                              PATTERN(up+)

                                              DEFINE

                                                up AS up.len > PREV (up.len)

                                             )

                            ORDER BY 1

                            /

                             

                            Now:

                             

                            SQL> variable names_per_row number
                            SQL> exec :names_per_row := 3;

                            PL/SQL procedure successfully completed.

                            SQL> WITH emp_ordered AS (
                              2                       SELECT  Row_Number() OVER(ORDER BY last_name || ', ' || first_name) rn,
                              3                               last_name || ', ' || first_name ename
                              4                         FROM  hr.employees e
                              5                      ),
                              6  emp_rsf (rn, name_list, len) AS (
                              7                                    SELECT  rn,
                              8                                            ename,
                              9                                            Length(ename)
                            10                                      FROM  emp_ordered
                            11                                      WHERE rn = 1
                            12                                   UNION ALL
                            13                                    SELECT  e.rn,
                            14                                            CASE
                            15                                              WHEN regexp_count(r.name_list,'; ') = :names_per_row - 1 THEN e.ename
                            16                                              ELSE r.name_list || '; ' || e.ename
                            17                                            END,
                            18                                            Length(
                            19                                                   CASE
                            20                                                     WHEN regexp_count(r.name_list,'; ') = :names_per_row - 1 THEN e.ename
                            21                                                     ELSE r.name_list || '; ' || e.ename
                            22                                                   END
                            23                                                  )
                            24                                      FROM      emp_rsf r
                            25                                            JOIN
                            26                                                emp_ordered e
                            27                                              ON e.rn = r.rn + 1
                            28                                  )
                            29  SELECT  name_list
                            30    FROM  emp_rsf
                            31    MATCH_RECOGNIZE(
                            32                    ORDER BY rn
                            33                    MEASURES up.name_list AS name_list,
                            34                             up.len AS len
                            35                    ONE ROW PER MATCH
                            36                    PATTERN(up+)
                            37                    DEFINE
                            38                      up AS up.len > PREV (up.len)
                            39                   )
                            40  ORDER BY 1
                            41  /

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            Abel, Ellen; Ande, Sundar; Atkinson, Mozhe
                            Austin, David; Baer, Hermann; Baida, Shelli
                            Banda, Amit; Bates, Elizabeth; Bell, Sarah
                            Bernstein, David; Bissot, Laura; Bloom, Harrison
                            Bull, Alexis; Cabrio, Anthony; Cambrault, Gerald
                            Cambrault, Nanette; Chen, John; Chung, Kelly
                            Colmenares, Karen; Davies, Curtis; De Haan, Lex
                            Dellinger, Julia; Dilly, Jennifer; Doran, Louise
                            Ernst, Bruce; Errazuriz, Alberto; Everett, Britney
                            Faviet, Daniel; Fay, Pat; Feeney, Kevin
                            Fleaur, Jean; Fox, Tayler; Fripp, Adam

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            Gates, Timothy; Gee, Ki; Geoni, Girard
                            Gietz, William; Grant, Douglas; Grant, Kimberely
                            Greenberg, Nancy; Greene, Danielle; Hall, Peter
                            Hartstein, Michael; Higgins, Shelley; Himuro, Guy
                            Hunold, Alexander; Hutton, Alyssa; Johnson, Charles
                            Jones, Vance; Kaufling, Payam; Khoo, Alexander
                            King, Janette; King, Steven; Kochhar, Neena
                            Kumar, Sundita; Ladwig, Renske; Landry, James
                            Lee, David; Livingston, Jack; Lorentz, Diana
                            Mallin, Jason; Markle, Steven; Marlow, James
                            Marvins, Mattea; Matos, Randall; Mavris, Susan

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            McCain, Samuel; McEwen, Allan; Mikkilineni, Irene
                            Mourgos, Kevin; Nayer, Julia; OConnell, Donald
                            Olsen, Christopher; Olson, TJ; Ozer, Lisa
                            Partners, Karen; Pataballa, Valli; Patel, Joshua
                            Perkins, Randall; Philtanker, Hazel; Popp, Luis
                            Rajs, Trenna; Raphaely, Den; Rogers, Michael
                            Russell, John; Sarchand, Nandita; Sciarra, Ismael
                            Seo, John; Sewall, Sarath; Smith, Lindsey
                            Smith, William; Stiles, Stephen; Sullivan, Martha
                            Sully, Patrick; Taylor, Jonathon; Taylor, Winston
                            Tobias, Sigal; Tucker, Peter; Tuvault, Oliver

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            Urman, Jose Manuel; Vargas, Peter; Vishney, Clara
                            Vollman, Shanta; Walsh, Alana; Weiss, Matthew
                            Whalen, Jennifer; Zlotkey, Eleni

                            36 rows selected.

                            SQL> exec :names_per_row := 4;

                            PL/SQL procedure successfully completed.

                            SQL> /

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            Abel, Ellen; Ande, Sundar; Atkinson, Mozhe; Austin, David
                            Baer, Hermann; Baida, Shelli; Banda, Amit; Bates, Elizabeth
                            Bell, Sarah; Bernstein, David; Bissot, Laura; Bloom, Harrison
                            Bull, Alexis; Cabrio, Anthony; Cambrault, Gerald; Cambrault, Nanette
                            Chen, John; Chung, Kelly; Colmenares, Karen; Davies, Curtis
                            De Haan, Lex; Dellinger, Julia; Dilly, Jennifer; Doran, Louise
                            Ernst, Bruce; Errazuriz, Alberto; Everett, Britney; Faviet, Daniel
                            Fay, Pat; Feeney, Kevin; Fleaur, Jean; Fox, Tayler
                            Fripp, Adam; Gates, Timothy; Gee, Ki; Geoni, Girard
                            Gietz, William; Grant, Douglas; Grant, Kimberely; Greenberg, Nancy
                            Greene, Danielle; Hall, Peter; Hartstein, Michael; Higgins, Shelley

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            Himuro, Guy; Hunold, Alexander; Hutton, Alyssa; Johnson, Charles
                            Jones, Vance; Kaufling, Payam; Khoo, Alexander; King, Janette
                            King, Steven; Kochhar, Neena; Kumar, Sundita; Ladwig, Renske
                            Landry, James; Lee, David; Livingston, Jack; Lorentz, Diana
                            Mallin, Jason; Markle, Steven; Marlow, James; Marvins, Mattea
                            Matos, Randall; Mavris, Susan; McCain, Samuel; McEwen, Allan
                            Mikkilineni, Irene; Mourgos, Kevin; Nayer, Julia; OConnell, Donald
                            Olsen, Christopher; Olson, TJ; Ozer, Lisa; Partners, Karen
                            Pataballa, Valli; Patel, Joshua; Perkins, Randall; Philtanker, Hazel
                            Popp, Luis; Rajs, Trenna; Raphaely, Den; Rogers, Michael
                            Russell, John; Sarchand, Nandita; Sciarra, Ismael; Seo, John

                            NAME_LIST
                            -------------------------------------------------------------------------------------
                            Sewall, Sarath; Smith, Lindsey; Smith, William; Stiles, Stephen
                            Sullivan, Martha; Sully, Patrick; Taylor, Jonathon; Taylor, Winston
                            Tobias, Sigal; Tucker, Peter; Tuvault, Oliver; Urman, Jose Manuel
                            Vargas, Peter; Vishney, Clara; Vollman, Shanta; Walsh, Alana
                            Weiss, Matthew; Whalen, Jennifer; Zlotkey, Eleni

                            27 rows selected.

                            SQL>

                             

                            SY.

                            • 27. Re: Multiple Rows Into One Column Field
                              padders

                              I think the requirements Brendan re-stated implied he intended to do it for length :-D

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

                                Oh, never mind my remark, Padders noticed already

                                 

                                Message was edited by: Hoek

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

                                  Now that I have installed 12c, I see that MATCH_RECOGNIZE can do a lot more.

                                  For future reference, when 12c is actually deployed:

                                   

                                  1) Assuming, as Tom did, that 3 values per column is hard-coded:

                                   

                                  SELECT * FROM scott.emp

                                  match_recognize (

                                    PARTITION BY deptno

                                    ORDER BY ename

                                    MEASURES

                                      A.ename || nvl2(b.ename, ','||b.ename, null

                                      || nvl2(c.ename, ','||c.ename, null) enames

                                    pattern (A b{0,1} c{0,1})

                                    define c as 1=1

                                  );

                                  DEPTNO ENAMES

                                      10 CLARK,KING,MILLER

                                      20 ADAMS,FORD,JONES 

                                      20 SCOTT,SMITH

                                      30 ALLEN,BLAKE,JAMES

                                      30 MARTIN,TURNER,WARD


                                  Notice you can't have an empty DEFINE clause, but you don't have to DEFINE every part of the pattern. I am using the defaults ONE ROW PER MATCH and AFTER MATCH SKIP PAST LAST ROW.


                                  2) Allowing for a variable number of values

                                   

                                  SELECT deptno,

                                  listagg(ename,',') WITHIN GROUP(ORDER BY ename) enames

                                  FROM scott.emp

                                  match_recognize (

                                    PARTITION BY deptno

                                    ORDER BY ename

                                    MEASURES match_number() mn

                                    all rows per match

                                    pattern (A*)

                                    define a AS count(*) <= 3

                                  )

                                  GROUP BY deptno, mn

                                  ORDER BY deptno, mn;


                                  [same output as 1) ]

                                   

                                  3) Output is limited by line size, not number of names

                                   

                                  SELECT deptno,

                                  listagg(ename,',') WITHIN GROUP(ORDER BY ename) enames

                                  FROM scott.emp

                                  match_recognize (

                                    PARTITION BY deptno

                                    ORDER BY ename

                                    MEASURES match_number() mn

                                    all rows per match

                                    pattern (A b*)

                                    subset ab = (A,b)

                                    define b as sum(length(ab.ename)+1) <= 17+1

                                  )

                                  GROUP BY deptno, mn

                                  order by deptno, mn;


                                  DEPTNO ENAMES

                                      10 CLARK,KING,MILLER

                                      20 ADAMS,FORD,JONES

                                      20 SCOTT,SMITH

                                      30 ALLEN,BLAKE,JAMES

                                      30 MARTIN,TURNER

                                      30 WARD

                                   

                                  This would be even cooler if LISTAGG() were supported within the MEASURES clause, because then MATCH_RECOGNIZE would do all the work. Unfortunately,

                                  ORA-62512: This aggregate is not yet supported in MATCH_RECOGNIZE clause.