1 2 Previous Next 15 Replies Latest reply: Nov 19, 2009 11:25 PM by Billy~Verreynne RSS

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

    647384
      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
          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
            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
              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
                Coool...Gotta have one those.

                (Preferably within the next five years)

                :-)
                • 5. Re: Multiple rows into a single line in 'Single Column Table'
                  647384
                  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
                    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'
                      Charles Hooper
                      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
                        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'
                          Billy~Verreynne
                          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
                            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'
                              Billy~Verreynne
                              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
                                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'
                                  Billy~Verreynne
                                  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
                                    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