This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Nov 4, 2012 8:13 PM by sb92075 RSS

Multiple rows into a single line in 'Single Column Table'

647384 Newbie
Currently Being Moderated
Hi,

I want to select Multiple rows into a single line in 'Single Column Table' .

For ex:

Employee table has only one column , named as empname . it has three rows

Select empname from emp;

empname
------------
thambi
peter
antony

My expected result: thambi,peter,antony

i did see some post regarding pivot query but did not get righ post for Single Column table.. Can someone help me.
  • 1. Re: Multiple rows into a single line in 'Single Column Table'
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As you found out, Pivot means produce separate columns from separate rows.
    What you want is 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.
  • 2. Re: Multiple rows into a single line in 'Single Column Table'
    108476 Journeyer
    Currently Being Moderated
    Hi,

    Oracle SQL does not have a direct mechanism for allowing multiple values from the same column to be displayed in the same row of output.

    SQL is designed not to disallow repeating values on a single line of output, but Oracle has several tricks for allowing repeating groups of same-column values on a single line of output.

    Here are several ways to pivot multiple rows into one row:

    http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

    Hope this helps . . .

    Donald K. Burleson
    Oracle Press author
    Author of "Oracle Tuning: The Definitive Reference"
    http://www.rampant-books.com/t_oracle_tuning_book.htm
    "Time flies like an arrow; Fruit flies like a banana".
  • 3. Re: Multiple rows into a single line in 'Single Column Table'
    MichaelS Guru
    Currently Being Moderated
    Oracle SQL does not have a direct mechanism for allowing multiple values from the same column to be displayed in the same row of output.
    Maybe not until 11gR2:
    SQL>  SELECT deptno,
           listagg (ename, ',') WITHIN GROUP (ORDER BY ename)
            enames
    FROM emp
    GROUP BY deptno
    /
        DEPTNO ENAMES                                            
    ---------- --------------------------------------------------
            10 CLARK,KING,MILLER                                 
            20 ADAMS,FORD,JONES,SCOTT,SMITH                      
            30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD              
    
    3 rows selected.
    ;)
  • 4. Re: Multiple rows into a single line in 'Single Column Table'
    Peter Gjelstrup Guru
    Currently Being Moderated
    Coool...Gotta have one those.

    (Preferably within the next five years)

    :-)
  • 5. Re: Multiple rows into a single line in 'Single Column Table'
    647384 Newbie
    Currently Being Moderated
    i encountered this question during an interview for Oracle 9i. the link which you provided has not shown for single column . Though we can archive this by 11g , i wish to know how to do it in Oracle 9i and that too using by single SELECT query.. NOT by using PL/SQL block.
  • 6. Re: Multiple rows into a single line in 'Single Column Table'
    MichaelS Guru
    Currently Being Moderated
    i wish to know how to do it in Oracle 9i
    That should work:
    SQL>  select deptno,
           rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
    from emp
    group by deptno
    /
        DEPTNO ENAMES                                  
    ---------- ----------------------------------------
            10 CLARK,MILLER,KING                       
            20 SMITH,FORD,ADAMS,SCOTT,JONES            
            30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD    
    
    3 rows selected.
  • 7. Re: Multiple rows into a single line in 'Single Column Table'
    CharlesHooper Expert
    Currently Being Moderated
    Nice example.

    A bit of a different approach using analytic functions:
    CREATE TABLE T20(
      DEPT NUMBER(10),
      EMPNAME VARCHAR2(30));
    
    INSERT INTO T20 VALUES (10,'THAMBI');
    INSERT INTO T20 VALUES (10,'PETER');
    INSERT INTO T20 VALUES (10,'ANTHONY');
    INSERT INTO T20 VALUES (20,'GEORGE');
    INSERT INTO T20 VALUES (20,'MICHAEL');
    
    COMMIT;
    
    COLUMN EMPNAME FORMAT A10
    
    SELECT
      DEPT,
      EMPNAME,
      ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY EMPNAME DESC) RN
    FROM
      T20;
    
     DEPT EMPNAME            RN
    ----- ---------- ----------
       10 THAMBI              1
       10 PETER               2
       10 ANTHONY             3
       20 MICHAEL             1
       20 GEORGE              2
    Now that the employees are separated by department, SYS_CONNECT_BY_PATH is used to draw the related employees into a single row:
    COLUMN EMP_LIST FORMAT A30
    
    SELECT
      DEPT,
      MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMPNAME,','),2)) EMP_LIST
    FROM
      (SELECT
        DEPT,
        EMPNAME,
        ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY EMPNAME DESC) RN
      FROM
        T20)
    CONNECT BY PRIOR 
      (DEPT||RN)=(DEPT||RN-1)
    START WITH
      RN=1
    GROUP BY
      DEPT;
    
     DEPT EMP_LIST
    ----- ------------------------------
       20 MICHAEL,GEORGE
       10 THAMBI,PETER,ANTHONY
    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 8. Re: Multiple rows into a single line in 'Single Column Table'
    716781 Newbie
    Currently Being Moderated
    burleson wrote:
    Hi,

    Oracle SQL does not have a direct mechanism for allowing multiple values from the same column to be displayed in the same row of output.

    SQL is designed not to disallow repeating values on a single line of output, but Oracle has several tricks for allowing repeating groups of same-column values on a single line of output.

    Here are several ways to pivot multiple rows into one row:

    http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

    Hope this helps . . .

    Donald K. Burleson
    Oracle Press author
    Author of "Oracle Tuning: The Definitive Reference"
    http://www.rampant-books.com/t_oracle_tuning_book.htm
    "Time flies like an arrow; Fruit flies like a banana".
    Hi,

    But Oracle 8i is enough smart to make this job, look at this:
    HR: XE > select * from phone_table;
    
    I PHONE
    - ----------
    1 11134134
    2 21132345
    3 31134134
    4 42345678
    5 51001100
    6 61231100
    7 71345100
    
    7 rows selected.
    
    HR: XE > select a1.phone|| ', ' ||a2.phone|| ', ' ||a3.phone|| ', ' ||a4.phone|| ', ' ||a5.phone|| '
    , ' ||a6.phone|| ', ' ||a7.phone all_phones
      2      from
      3  (select x.id, x.phone from phone_table x) a1,
      4  (select x.id, x.phone from phone_table x) a2,
      5  (select x.id, x.phone from phone_table x) a3,
      6  (select x.id, x.phone from phone_table x) a4,
      7  (select x.id, x.phone from phone_table x) a5,
      8  (select x.id, x.phone from phone_table x) a6,
      9  (select x.id, x.phone from phone_table x) a7
     10      where
     11  a1.id >a2.id and
     12  a2.id >a3.id and
     13  a3.id >a4.id and
     14  a4.id >a5.id and
     15  a5.id >a6.id and
     16  a6.id >a7.id ;
    
    ALL_PHONES
    --------------------------------------------------------------------------------
    71345100, 61231100, 51001100, 42345678, 31134134, 21132345, 11134134
    
    HR: XE > 
    The idea is to use a key and a little imagination.

    Regards,
    Ion
  • 9. Re: Multiple rows into a single line in 'Single Column Table'
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    burleson wrote:

    Oracle SQL does not have a direct mechanism for allowing multiple values from the same column to be displayed in the same row of output.
    Incorrect. A column can be a collection that holds multiple values (in a structured way too, and not some restrictive string aggregation technique).
    SQL> -- define the collection data type 
    SQL> create or replace type TStrings is table of varchar2(4000);
      2  /
    
    Type created.
    
    SQL>
    SQL> -- use MULTISET() to construct that collection dynamically in a SQL projection
    SQL> select
      2          d.dname,
      3          cast( multiSet( select e.ename from emp e where e.deptno = d.deptno order by 1) as TStrings ) as EMPLOYEE_LIST
      4  from       dept d
      5  order by d.dname
      6  /
    
    DNAME                EMPLOYEE_LIST
    -------------------- --------------------------------------------------------------------------------
    ACCOUNTING           TSTRINGS('CLARK', 'KING', 'MILLER')
    OPERATIONS           TSTRINGS()
    RESEARCH             TSTRINGS('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
    SALES                TSTRINGS('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')
    
    SQL>
    The above works on 10g. May also have worked in on 9i.
  • 10. Re: Multiple rows into a single line in 'Single Column Table'
    647384 Newbie
    Currently Being Moderated
    Thanks for all the reples. Yes I tried with XML functions.. as below..

    select
    rtrim (xmlagg (xmlelement (e, emp_name || ',')).extract ('//text()'), ',') Empnames
    from emp

    Its working. As I already said that I have only one column in the table, have no department column. If i have deaprtment column, other solultions are working fine. Thanks a lot.

    Regards,
    Thambi
  • 11. Re: Multiple rows into a single line in 'Single Column Table'
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user644381 wrote:

    Its working. As I already said that I have only one column in the table, have no department column. If i have deaprtment column, other solultions are working fine.
    None of the solutions shown requires a department column specifically. My example uses it.. as an example. The MULTISET function is a standard SQL function. And how you choose to use it does not depend on having a specific column at all!.

    You want a single row with all the employees listed? Then the following can be used:
    SQL> select cast( multiSet( select e.ename from emp e ) as TStrings ) as LIST from dual;
    
    LIST
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    TSTRINGS('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')
    The question that you need to ask yourself is why use such a technique? For rendering the data a specific way in the client? Well, rendering data is NOT a SQL function and in essence a result of ignorance of how to correctly use client-server. Rendering on the client is dealt with by the client itself. Using SQL to do it.. not only nasty (as many of these examples above are), but also far from optimal and efficient SQL. And in most cases, will not scale. Increase the data volume of the table queried and there will be a hefty performance knock as SQL is incorrectly used.
  • 12. Re: Multiple rows into a single line in 'Single Column Table'
    647384 Newbie
    Currently Being Moderated
    Hi ,

    yes your solution will work. since i don't have privilege to create collection data type in my test database, I can't test it. Logically, your solution should be working. and also you have shown the example. I was asked this question by an interviewer, that's why i posted in forum. Thanks for your help.


    Regards,
    Thamib
  • 13. Re: Multiple rows into a single line in 'Single Column Table'
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user644381 wrote:

    yes your solution will work. since i don't have privilege to create collection data type in my test database, I can't test it.
    You should request the access piv for creating types. There is really no "+harm+" or security issues in allowing this right. If you can create tables, you should be able to create types too.
    I was asked this question by an interviewer, that's why i posted in forum.
    Silly question IMO. Reason why I say it is that the actual problem is not presented. The interviewer thinks he/she knows the ideal solution to a problem. And then want you to tell them how to implement that solution. Sorry, this is not how development works. They want someone to simply generate code for them? They should get a case tool, code monkey or something.

    Developers are employed to do two basic things:
    - solve technical problems
    - write technical solutions

    Just throwing the 2nd part at the developer, writing the technical solution, without allowing the developer to participate in the solving part is a major mistake.
  • 14. Re: Multiple rows into a single line in 'Single Column Table'
    Centinul Guru
    Currently Being Moderated
    Billy  Verreynne  wrote:
    The question that you need to ask yourself is why use such a technique? For rendering the data a specific way in the client? Well, rendering data is NOT a SQL function and in essence a result of ignorance of how to correctly use client-server. Rendering on the client is dealt with by the client itself. Using SQL to do it.. not only nasty (as many of these examples above are), but also far from optimal and efficient SQL. And in most cases, will not scale. Increase the data volume of the table queried and there will be a hefty performance knock as SQL is incorrectly used.
    Billy --

    I would like to stem a little discussion from this point.

    Where do you draw the line between the correct use of SQL or not? Or between rendering on the client and just returning data?

    Now with LISTAGG, PIVOT and UNPIVOT all available to us would these be considered correct uses of SQL?

    Where does this leave the TO_CHAR function? Is this considered rendering?

    I'm fully expecting a fuzzy answer with something along the lines of "do the work where it makes the most sense" from a ease of development and maintainability perspective but I just wanted to ask.

    Thanks! :)
1 2 Previous Next

Legend

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