This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Oct 6, 2013 3:28 AM by BrendanP Go to original post RSS
  • 15. Re: Multiple Rows Into One Column Field
    Etbin Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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;
  • 17. Re: Multiple Rows Into One Column Field
    Hoek Guru
    Currently Being Moderated

    Nice!

  • 18. Re: Multiple Rows Into One Column Field
    BluShadow Guru Moderator
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    Exactly what I was after! (including deptno)

  • 21. Re: Multiple Rows Into One Column Field
    tkyte Employee ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    Oh, never mind my remark, Padders noticed already

     

    Message was edited by: Hoek

  • 29. Re: Multiple Rows Into One Column Field
    Stew Ashton Expert
    Currently Being Moderated

    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.


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points