1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Display Oracle SQL output rows on one single line

Discussion in 'SQL PL/SQL' started by orafan, Mar 18, 2009.

  1. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    Hi again :(

    You guys rock and i am really thankful for all the help. As it is i am learning my way with sql pl/sql.

    Now i have run into another roadblock. Basically I have output from a query which produces a single column output in many rows. The query is
    Code (Text):

    SELECT REPLACE (attribute1, '_', '/') || ',' || attr_val
      FROM users_upload_new
     WHERE empid = '140' AND attr_val IS NOT NULL
     
    This produces the output
    Code (Text):

    COM/PRJ/STEEL,7
    COM/PRJ/COMMON/ADM,7
    COM/PRJ/COMMON/CMRCL,7
    -----
    -----
     
    Now actually I want the output as
    Code (Text):

    COM/PRJ/STEEL,7,COM/PRJ/COMMON/ADM,7,COM/PRJ/COMMON/CMRCL,7
     
    The comma separation is fine, Basically i want the multiple rows into a single row. Or you can say even multiple rows into a single row multiple column would do. i know I have to use SYS_CONNECT_BY_PATH but i can't seem to figure it out.

    It's ok if you don't give me full code, just some pointers would be great. Thanks for your time and help.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
  3. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hi sadik.. thanks i tried both with decode and group and also with sys_connect_by_path the way rajavu showed but not working :(

    I guess i will do it in excel...
     
  4. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hiya!!! absolutely brilliant.. it worked out

    This is the code. First I created a table with my previous query
    Code (Text):

    create table users_upload_final as
    SELECT empid, REPLACE (attribute1, '_', '/') || '-' || attr_val accnt
      FROM users_upload_new
     WHERE attr_val IS NOT NULL
     order by empid
     
    Then I used Rajavu's method of SYS_CONNECT_BY_PATH to concatenate rows into comma separated values as below
    Code (Text):

    SELECT     empid, LTRIM (MAX (SYS_CONNECT_BY_PATH (accnt, ',')), ',') accnt1
          FROM (SELECT empid, accnt,
                       ROW_NUMBER () OVER (PARTITION BY empid ORDER BY ROWNUM) rn
                  FROM users_upload_final)
    CONNECT BY empid = PRIOR empid AND rn = PRIOR rn + 1
    START WITH rn = 1
      GROUP BY empid
      ORDER BY empid
     
    Thanks for that excellent article Rajavu! :) U r a genius man!
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This can be done without creating temporary table by using another trick as follows.

    Code (Text):

    SQL>  select  * from  users_upload_new ;

         EMPID ATTRIBUTE1                       ATTR_VAL
    ---------- ------------------------------ ----------
          1000 COM,PRJ,STEEL                           7
          1000 COM,PRJ,COMMON,ADM                      7
          1000 COM,PRJ,COMMON,CMRCL                    7
          1000 COM,PRJ,COMMON,ABCL                     7
          2000 DOM,PRJ,STEEL                           5
          2000 DOM,PRJ,COMMON,ADM                      5
          2000 DOM,PRJ,COMMON,CMRCL                    5

    7 rows selected.
     
    Code (Text):

    SQL> SELECT     EMPID  ,
      2              MAX(REPLACE( REPLACE(REPLACE(LTRIM(SYS_CONNECT_BY_PATH (ACCNT,'*'),'*'),';','/'),'_',','),'*',',')) ACCNT1
      3  FROM ( SELECT EMPID,
      4                REPLACE (ATTRIBUTE1, ',', ';') || '_' || ATTR_VAL ACCNT ,
      5                ROW_NUMBER () OVER (PARTITION BY EMPID ORDER BY ROWNUM) RN
      6         FROM USERS_UPLOAD_NEW )
      7  CONNECT BY EMPID = PRIOR EMPID AND RN = PRIOR RN + 1
      8  START WITH RN = 1
      9    GROUP BY EMPID
     10    ORDER BY EMPID;

         EMPID    ACCNT1
    ----------   --------------------------------------------------------------------------------

          1000    COM/PRJ/STEEL,7,COM/PRJ/COMMON/ADM,7,COM/PRJ/COMMON/CMRCL,7,COM/PRJ/COMMON/ABCL,7

          2000    DOM/PRJ/STEEL,5,DOM/PRJ/COMMON/ADM,5,DOM/PRJ/COMMON/CMRCL,5

     
    Ensure that there is no such replacing chracters like ';','_' and '*' in actual filed

    Also Another Method

    Code (Text):

    SQL> SELECT     EMPID  ,  MAX(DECODE ( RN ,1 , ACCNT )) ||
      2                       MAX(DECODE ( RN ,2 , ','||ACCNT )) ||
      3                       MAX(DECODE ( RN ,3 , ','||ACCNT )) ||
      4                       MAX(DECODE ( RN ,4 , ','||ACCNT )) ||
      5                       MAX(DECODE ( RN ,5 , ','||ACCNT )) ||
      6                       MAX(DECODE ( RN ,6 , ','||ACCNT )) ||
      7                       MAX(DECODE ( RN ,7 , ','||ACCNT )) ACCNT1            
      8        FROM (
      9  SELECT EMPID, REPLACE (ATTRIBUTE1, ',', '/') || ',' || ATTR_VAL ACCNT ,
     10                     ROW_NUMBER () OVER (PARTITION BY EMPID ORDER BY ROWNUM) RN
     11                FROM USERS_UPLOAD_NEW
     12                )
     13  CONNECT BY EMPID = PRIOR EMPID AND RN = PRIOR RN + 1
     14  START WITH RN = 1
     15    GROUP BY EMPID
     16    ORDER BY EMPID;

         EMPID    ACCNT1
    ----------   --------------------------------------------------------------------------------

          1000    COM/PRJ/STEEL,7,COM/PRJ/COMMON/ADM,7,COM/PRJ/COMMON/CMRCL,7,COM/PRJ/COMMON/ABCL,7

          2000    DOM/PRJ/STEEL,5,DOM/PRJ/COMMON/ADM,5,DOM/PRJ/COMMON/CMRCL,5

    SQL>
     
    This code assumes that each EMPID is having maximum 7 ATTRIBUTE1s
     
  6. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    hey! wow... man can i buy u a drink online?? :) thanks a lot. Actually my issue got resolved yesterday itself with the temp table. But yes as you pointed out it could have been done otherwise too. I have saved this page for future references!