8 Replies Latest reply: May 29, 2012 4:48 AM by 940129 RSS

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

    900988
      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
          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
          • 3. Re: i wanted to display the multiple rows in one row but column should be diff
            Hoek
            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
              /* 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
                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
                  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
                    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
                      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