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!

split data separated in different columns

Discussion in 'SQL PL/SQL' started by atirtil, Jun 17, 2013.

  1. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    This is the data I get :

    OWNERS
    -----------------------------------------------------
    Nutten Sophia, Philippe St├ęphane, Coni Honi, Poni Koni
    ------------------------------------------------------

    what i want is:

    OWNER1 OWNER2 OWNER3 OWNER4
    NuttenSoph Philippe .. ...


    so there will be a number of columns equal to the number of owners, the number is changing, i can get this number from another query so you can accept that there are only 3 for example

    the query:

    Code (Text):

    select
       name.name
    from name
    left join casename on name.namekey = casename.namekey
    where
        nametypekey = 209 and casename.casekey = 44
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Presuming you're using 11.2 you can use the pivot operator to do just that. As an example of dynamic column naming with the pivot operator look at the code below. To begin a table is created and populated that provides the data which needs to be converted:

    To begin a table is created and populated that provides the data which needs to be converted:

    Code (SQL):
     CREATE TABLE name_qty(
      2          name            varchar2(20),
      3          quantity        NUMBER
      4  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO name_qty
      3  VALUES('COL_A', 5000)
      4  INTO name_qty
      5  VALUES('COL_B', 3000)
      6  INTO name_qty
      7  VALUES('COL_C', 3000)
      8  SELECT * FROM dual;
     
    3 ROWS created.
     
    SQL> SELECT name, quantity
      2  FROM name_qty;
     
    NAME                   QUANTITY
    -------------------- ----------
    COL_A                      5000
    COL_B                      3000
    COL_C                      3000
     
    SQL>
    Three column names and three associated values. The goal is to associate the proper value with the proper column name so the next step in this process is to generate a list of values comprised of the key data, the data that will name the new columns in the output. Using the WM_CONCAT function this is a fairly easy task:

    Code (SQL):

    SQL> COLUMN namelist new_value n_list noprint
    SQL>
    SQL> SELECT wm_concat(''''||name||'''') namelist
      2  FROM name_qty
      3  CONNECT BY nocycle name = prior name
      4  GROUP BY level;
     

    SQL>
    To keep the output of the entire process uncluttered the resulting column from the query shown above is not printed. [For reference purposes the output at this stage of the solution would be ('COL_A','COL_B','COL_C'). Note also the use of the NEW_VALUE SQL*Plus function to associate the column output with a variable name that can be used later in the script. That step allows the use of the generated string in the resulting pivot query.] This is done since both the previous query and the query shown below will be placed in a single script to generate the columnar output. The script is generating a list to be used by the pivot operator to generate column names and associate the correct value with the proper column. Now it's time to write the workhorse of this solution, the pivot query itself, which requires an aggregate on which to operate. SUM() is chosen in this example as the resulting value is computed by grouping on the NAME column, which will not change the value to be displayed:

    Code (SQL):
     SELECT *
      2  FROM
      3  (SELECT name, quantity
      4   FROM name_qty nq
      5  )
      6  pivot
      7  (
      8          SUM(quantity)
      9          FOR name IN (&n_list)
     10  );
     
       'COL_A'    'COL_C'    'COL_B'
    ---------- ---------- ----------
          5000       3000       3000
     
    SQL>
    The effort is successful as correct values are associated with the proper column names. What makes this solution so convenient is the fact that rows can be added to the source table and the pivoted output will not be missing any columns. Adding data to the table and running the script again generates the following output:

    Code (SQL):
     INSERT ALL
      2  INTO name_qty
      3  VALUES('COL_D', 5000)
      4  INTO name_qty
      5  VALUES('COL_E', 3000)
      6  INTO name_qty
      7  VALUES('COL_F', 3000)
      8  SELECT * FROM dual;
     
    3 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT wm_concat(''''||name||'''') namelist
      2  FROM name_qty
      3  CONNECT BY nocycle name = prior name
      4  GROUP BY level;
     

    SQL>
    SQL> SELECT *
      2  FROM
      3  (SELECT name, quantity
      4   FROM name_qty nq
      5  )
      6  pivot
      7  (
      8          SUM(quantity)
      9          FOR name IN (&n_list)
     10  );
     
       'COL_A'    'COL_F'    'COL_E'    'COL_D'    'COL_C'    'COL_B'
    ---------- ---------- ---------- ---------- ---------- ----------
          5000       3000       3000       5000       3000       3000
     
    SQL>
    The script didn't change but the output now correctly reflects the addition of three rows to the source table, and, as a result, three new columns in the pivoted output. This format also makes the data easier to read and understand.
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    That was a good way. But since, WM_CONCAT is an undocumented function and also not supported by Oracle, i'd recommend the following: (Frame it as required)
    Code (SQL):

    SELECT  *    
    FROM    (SELECT rtrim(xmlagg(xmlelement(e,e1.ename||',') ORDER BY ename).EXTRACT('//text()'),',')  enames
                   ,d1.dname
             FROM   scott.emp   e1
                   ,scott.dept  d1
             WHERE  d1.deptno   =   e1.deptno
             GROUP BY   d1.dname  
            )
            Pivot   (MIN(enames)
                     FOR (dname) IN ('ACCOUNTING','SALES','RESEARCH','OPERATIONS')
                    );
     
    Or else, if u're using 11g R2 or above then u can use LISTAGG as well..
    Link
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It is a perfectly acceptable way for releases older than 11g; the example posted was written to address the same issue in a 10.2 database.

    To update the example for 11.2 and include the xmlagg example (although listagg appears to be easier and cleaner to code):

    Code (SQL):
    SQL> SQL> CREATE TABLE name_qty(
    2 name varchar2(20),
    3 quantity NUMBER
    4 );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
    2 INTO name_qty
    3 VALUES('COL_A', 5000)
    4 INTO name_qty
    5 VALUES('COL_B', 3000)
    6 INTO name_qty
    7 VALUES('COL_C', 3000)
    8 SELECT * FROM dual;
     
    3 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT name, quantity
    2 FROM name_qty;
     
    NAME QUANTITY
    -------------------- ----------
    COL_A 5000
    COL_B 3000
    COL_C 3000
     
    SQL>
    SQL> COLUMN namelist new_value n_list noprint
    SQL>
    SQL> SELECT listagg(''''||name||'''',',') WITHIN GROUP (ORDER BY name) namelist
    2 FROM name_qty
    3 CONNECT BY nocycle name = prior name
    4 GROUP BY level;
     
     
    SQL>
    SQL> SELECT *
    2 FROM
    3 (SELECT name, quantity
    4 FROM name_qty nq
    5 )
    6 pivot
    7 (
    8 SUM(quantity)
    9 FOR name IN (&n_list)
    10 );
     
    'COL_A' 'COL_B' 'COL_C'
    ---------- ---------- ----------
    5000 3000 3000
     
    SQL>
    SQL> SELECT *
    2 FROM (SELECT rtrim(xmlagg(xmlelement(e,name||',') ORDER BY name).EXTRACT('//text()'),',') names
    3 ,quantity
    4 FROM name_qty
    5 GROUP BY name,quantity
    6 )
    7 Pivot (SUM(quantity)
    8 FOR names IN (&n_list)
    9 );
     
    'COL_A' 'COL_B' 'COL_C'
    ---------- ---------- ----------
    5000 3000 3000
     
    SQL>
    SQL>
    SQL> INSERT ALL
    2 INTO name_qty
    3 VALUES('COL_D', 5000)
    4 INTO name_qty
    5 VALUES('COL_E', 3000)
    6 INTO name_qty
    7 VALUES('COL_F', 3000)
    8 SELECT * FROM dual;
     
    3 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT listagg(''''||name||'''',',') WITHIN GROUP (ORDER BY name) namelist
    2 FROM name_qty
    3 CONNECT BY nocycle name = prior name
    4 GROUP BY level;
     
     
    SQL>
    SQL> SELECT *
    2 FROM
    3 (SELECT name, quantity
    4 FROM name_qty nq
    5 )
    6 pivot
    7 (
    8 SUM(quantity)
    9 FOR name IN (&n_list)
    10 );
     
    'COL_A' 'COL_B' 'COL_C' 'COL_D' 'COL_E' 'COL_F'
    ---------- ---------- ---------- ---------- ---------- ----------
    5000 3000 3000 5000 3000 3000
     
    SQL>
    SQL> SELECT *
    2 FROM (SELECT rtrim(xmlagg(xmlelement(e,name||',') ORDER BY name).EXTRACT('//text()'),',') names
    3 ,quantity
    4 FROM name_qty
    5 GROUP BY name,quantity
    6 )
    7 Pivot (SUM(quantity)
    8 FOR names IN (&n_list)
    9 );
     
    'COL_A' 'COL_B' 'COL_C' 'COL_D' 'COL_E' 'COL_F'
    ---------- ---------- ---------- ---------- ---------- ----------
    5000 3000 3000 5000 3000 3000
     
    SQL>
     
    It is nice to know that the previously supplied xmlagg example works; simply posting code wthout providing actual output generated by it only does half of the job of assisting someone. A typo can cause posted code to fail to function and some here may think that they are at fault when it doesn't work.

    WM_CONCAT is supplied by Oracle in the Workspace Manager product so it is suppported by Oracle, possibly not for independent use such as this, but it is supported.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    From what I see SRL should be in the FOR clause; as an example:

    FOR SRL in 1,2,3,4,5,6,7,8,9

    or from 1 to the maximum value SRL can attain.
     
  6. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I forgot to mention, I need the column name to start with a letter. It is not for my pleasure.(I do create bookmarks on microsoft word with these columns alias and bookmarks should start with a letter, not a digit).

    If i do it as you said for srl in(1..7). I do get 1_BREINV 1_BRENUMINV ...2_BREINV 2_BRENUMINV
    anyway to change it to : BREINV_1 BRENUMINV_1 .. BREINV_2 BRENUMINV_2 ?

    thanks a lot
     
  7. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Dude !!

    You're carrying two duplicate threads simultaneously. Which one should we follow?
    Plz do what is obvious.
     
    atirtil likes this.
  8. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    yes I am sorry, just follow the last one. Thats the final point I am in at the moment. Sorry about it