This discussion is archived
7 Replies Latest reply: Nov 19, 2012 3:43 AM by BluShadow RSS

Dynamic number of columns and splitting

ediface Newbie
Currently Being Moderated
Hi,

I have a column which contains data like this:

"Person 1-100||Person 2-678||Person 3-1500"

Is there any way this can be returned (either in SQL or PL/SQL) so it dynically generates the number of columns needed for the different people? So in the example above I would get back 3 columns, titled Person 1, Person 2 and person 3 and containing their values:

Person 1 Person 2 Person 3
100 678 1500

It needs to be dynamic as the number of People can range from non (in which case it is null) to many.

The format of the data I am trying to split up will always be the same, (name of Person)-(value)||(name of Person)-(value)....

Thanks for any tips/pointers
  • 1. Re: Dynamic number of columns and splitting
    ediface Newbie
    Currently Being Moderated
    Should have read your FAQ first, just found PL/SQL 101 : Cursors and SQL Projection

    Thanks :)
  • 2. Re: Dynamic number of columns and splitting
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    The first tip I have is:
    get rid of that column and turn it into a proper one to many relationship.

    Beside that <tt>regexp_substr</tt> could be your friend or <tt>instr</tt> if you rather count positions.

    bye
    TPD
  • 3. Re: Dynamic number of columns and splitting
    BluShadow Guru Moderator
    Currently Being Moderated
    Not sure why you've marked your question as answered already... :-/

    The number of columns in an SQL statement cannot simply be dynamic and certainly cannot be determined from the data itself, without actually writing dyanamic code to generate the query itself.

    Read this: {thread:id=2309172}

    You should also consider fixing the structure of your database so that it is properly designed with relational tables. Storing multiple values in a CSV style inside a single column is not how data should be stored in a database.
  • 4. Re: Dynamic number of columns and splitting
    Gurujothi Explorer
    Currently Being Moderated
    Hi ,

    You can try this if your data is same format,
    CREATE TABLE sample_table(str VARCHAR2(100));
    
    
    INSERT INTO sample_table
         VALUES ('Person 1-100||Person 2-678||Person 3-1500');
    
    
    SELECT SUBSTR (str, INSTR (str, '-', 1) + 1, 3) AS "Person 1",
           SUBSTR (str, INSTR (str, '-', 1, 2) + 1, 3) AS "Person 2",
           SUBSTR (str, INSTR (str, '-', 1, 3) + 1) AS "Person 3"
      FROM sample_table;
    Regards,
    Guru
  • 5. Re: Dynamic number of columns and splitting
    ediface Newbie
    Currently Being Moderated
    Marked as answered as I just found that one myself :) but thanks

    Unfortunately I don't have control of the DB structure and am just selecting data from it but thanks again.
  • 6. Re: Dynamic number of columns and splitting
    ediface Newbie
    Currently Being Moderated
    Thanks Gurujothi, that answers the string splitting part and works great, unfortunately doesn't solve the dynamic number of columns part but as the link me and BluShadow gave there's a little more to that. I'm going to do some reading :) cheers
  • 7. Re: Dynamic number of columns and splitting
    BluShadow Guru Moderator
    Currently Being Moderated
    ediface wrote:
    Marked as answered as I just found that one myself :) but thanks

    Unfortunately I don't have control of the DB structure and am just selecting data from it but thanks again.
    Then the best thing to do is to extract as many as the maximum expected, and have your code or whatever test for null values to ignore the ones that aren't found

    e.g. assuming you can have at most 4 people...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'Person 1-100||Person 2-678||Person 3-1500' as txt from dual union all
      2             select 'Person 4-100||Person 5-678||Person 6-1500||Person 7-2000' from dual)
      3  --
      4  -- end of test data
      5  --
      6  select regexp_substr(txt, '[^|]+',1,1) as person1
      7        ,regexp_substr(txt, '[^|]+',1,2) as person2
      8        ,regexp_substr(txt, '[^|]+',1,3) as person3
      9        ,regexp_substr(txt, '[^|]+',1,4) as person4
     10* from t
    SQL> /
    
    PERSON1           PERSON2           PERSON3           PERSON4
    ----------------- ----------------- ----------------- -----------------
    Person 1-100      Person 2-678      Person 3-1500
    Person 4-100      Person 5-678      Person 6-1500     Person 7-2000
    
    SQL>
    Or... you unpivot the data...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 1 as id, 'Person 1-100||Person 2-678||Person 3-1500' as txt from dual union all
      2             select 2, 'Person 4-100||Person 5-678||Person 6-1500||Person 7-2000' from dual)
      3  --
      4  -- end of test data
      5  --
      6  select id, level as per_id, regexp_substr(txt, '[^|]+', 1, level) as person
      7  from   t
      8  where  regexp_substr(txt, '[^|]+', 1, level) is not null
      9  connect by id = prior id
     10          and prior sys_guid() is not null
     11*         and level <= (select max((length(regexp_replace(txt,'[^|]'))+2)/2) from t)
    SQL> /
    
            ID     PER_ID PERSON
    ---------- ---------- --------------------------------------------------------
             1          1 Person 1-100
             1          2 Person 2-678
             1          3 Person 3-1500
             2          1 Person 4-100
             2          2 Person 5-678
             2          3 Person 6-1500
             2          4 Person 7-2000
    
    7 rows selected.

Legend

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