This discussion is archived
8 Replies Latest reply: May 29, 2012 2:48 AM by 940129 RSS

i wanted to display the multiple rows in one row but column should be diff

900988 Newbie
Currently Being Moderated
Hi

Could any body help me regarding this query how to write to get the multiple rows in one row.

eg.

i have one table tab1(eno number,ename varchar2,uid1 varchar2,uid2 varchar2,uid3 varchar4)

but when i am runing the query I am getting multiple record against one eno number because of uid1,uid2,uid3
suppose value of table is

eno ename uid1 uid2 uid3
--------------------------------------------
1 a u1
1 a u2
1 a u3

when i am quering it is coming same as above but I want in one row
eno ename uid1 uid2 uid3
---------------------------------------------
1 a u1 u2 u3


can any onle help me how to write the query for this requirement.


thanks
saif
  • 1. Re: i wanted to display the multiple rows in one row but column should be diff
    908002 Expert
    Currently Being Moderated
    select eno, ename, max(decode(uid1, 'u1', 'u1')) uid1,max(decode(uid1, 'u2', 'u2')) uid2,max(decode(uid1, 'u3', 'u3')) uid3
    from tablename
    group by eno,ename
  • 2. Re: i wanted to display the multiple rows in one row but column should be diff
    Saubhik Guru
    Currently Being Moderated
    {message:id=9360005}
  • 3. Re: i wanted to display the multiple rows in one row but column should be diff
    Hoek Guru
    Currently Being Moderated
    How about:
    SQL> create table tab1 as
      2  select 1 eno, 'a' ename, 'u1' uid1, null uid2, null uid3 from dual union all
      3  select 1, 'a', null, 'u2', null from dual union all
      4  select 1, 'a', null, null, 'u3' from dual;
    
    Table created.
    
    SQL> select * from tab1;
    
           ENO E UI UI UI
    ---------- - -- -- --
             1 a u1
             1 a    u2
             1 a       u3
    
    SQL> select eno
      2  ,      ename
      3  ,      max(uid1)
      4  ,      max(uid2)
      5  ,      max(uid3)
      6  from   tab1
      7  group by eno, ename;
    
           ENO E MA MA MA
    ---------- - -- -- --
             1 a u1 u2 u3
    Search this forum for PIVOT to find more examples...
  • 4. Re: i wanted to display the multiple rows in one row but column should be diff
    indra budiantho Expert
    Currently Being Moderated
    /* Formatted on 2012/05/29 15:51 (Formatter Plus v4.8.8) */
    WITH t AS
         (SELECT 1 col1, 'a' col2, 'u1' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'a' col2, 'u2' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'a' col2, 'u3' col3
            FROM DUAL)
    SELECT   xx.col1, MAX (DECODE (xx.rn, 1, col3)) AS uid1, MAX (DECODE (xx.rn, 2, col3)) AS uid2, MAX (DECODE (xx.rn, 3, col3)) AS uid3
        FROM (SELECT ROWNUM rn, t2.*
                FROM t t2) xx
    GROUP BY col1;
  • 5. Re: i wanted to display the multiple rows in one row but column should be diff
    Venkadesh Raja Pro
    Currently Being Moderated
    i don't understand your requirement exactly.But may be your looking this
    with test as
    (
    select 1 enno,'a' ename,'u1' uid1 from dual
    union all
    select 1,'a','u2' from dual
    union all
    select 1,'a','u3' from dual
    )
    select enno,listagg(ename,' ') within group(order by ename)ename,listagg(uid1,' ') within group(order by uid1) u
    from test
    group by enno;
    
    
    
    ENNO     ENAME     U
    1     a a a     u1 u2 u3
    Thanks

    Venkadesh
  • 6. Re: i wanted to display the multiple rows in one row but column should be diff
    900988 Newbie
    Currently Being Moderated
    but the column uid1 , uid2 and uid3 is not hard coded it may have different value so i cant make it hard cord in query
  • 7. Re: i wanted to display the multiple rows in one row but column should be diff
    indra budiantho Expert
    Currently Being Moderated
    which is hard coded in my code? Here another approach, but fail for c as there is no information for the value of a column: does 1 in u1 means col 1, etc.
    /* Formatted on 2012/05/29 16:29 (Formatter Plus v4.8.8) */
    WITH t AS
         (SELECT 1 col1, 'a' col2, 'u1' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'a' col2, 'u2' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'a' col2, 'u3' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'b' col2, 'u1' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'b' col2, 'u3' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'b' col2, 'u2' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'c' col2, 'u1' col3
            FROM DUAL
          UNION ALL
          SELECT 1 col1, 'c' col2, 'u3' col3
            FROM DUAL)
    SELECT   xx.col1, xx.col2, MAX (DECODE (xx.rn, 1, col3)) AS uid1, MAX (DECODE (xx.rn, 2, col3)) AS uid2,
             MAX (DECODE (xx.rn, 3, col3)) AS uid3
        FROM (SELECT t.col1, t.col2, t.col3, ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3) rn
                FROM t) xx
    GROUP BY col1, col2;
    output:

    COL1     COL2     UID1     UID2     UID3

    1     a     u1     u2     u3
    1     b     u1     u2     u3
    1     c     u1     u3

    Edited by: ʃʃp on May 29, 2012 2:30 AM
  • 8. Re: i wanted to display the multiple rows in one row but column should be diff
    940129 Newbie
    Currently Being Moderated
    Just modifying 586's solution(which is correct).

    WITH t AS
    (SELECT 1 col1, 'a' col2, 'u1' col3
    FROM DUAL
    UNION ALL
    SELECT 1 col1, 'a' col2, 'u2' col3
    FROM DUAL
    UNION ALL
    SELECT 1 col1, 'a' col2, 'u3' col3
    FROM DUAL)
    SELECT xx.col1,xx.col2, MAX (DECODE (xx.rn, 1, col3)) AS uid1, MAX (DECODE (xx.rn, 2, col3)) AS uid2, MAX (DECODE (xx.rn, 3, col3)) AS uid3
    FROM (SELECT ROWNUM rn, t2.*
    FROM t t2) xx
    GROUP BY col1,col2;


    Output:
    COL1 COL2 UID1 UID2 UID3
    ------ ------- ------ ------ ------
    1 a u1 u2 u3

    Edited by: 937126 on May 29, 2012 2:45 AM

    Edited by: 937126 on May 29, 2012 2:48 AM

Legend

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