Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: Create new rows for each id based on location

    Hi All,

    I have data in a table as below,


    id,id2,location
    1, k12, CA, WA, NY
    2,K8,VA,TX

    can someone please tell me how can I get desired output as shown below.

    expected output:
    id,id2,location
    1,k12,CA
    1,k12,WA
    1,k12,NY
    2,k8,VA
    2,k8,TX

    Thanks

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Make an append query using the 1st 3 fields.
    Then remove the 3rd fld, and replace with the 4th.
    Run append.
    Etc.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just as a life lesson. The states should have been in a daughter table, one state per row. Never use a single column to hold multiple pieces of information.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by ranman256 View Post
    Make an append query using the 1st 3 fields.
    Then remove the 3rd fld, and replace with the 4th.
    Run append.
    Etc.
    What is an "append query"? Could you post a query which demonstrates that, please?

    In the meantime, here's one option (Oracle 11g):
    Code:
    SQL> with test as
      2    (select 1 id, 'k12' id2, 'CA, WA, NY' location from dual union all
      3     select 2,    'k8'     , 'VA, TX'              from dual
      4    )
      5  select
      6    id,
      7    id2,
      8    regexp_substr(location, '[^, ]+', 1, column_value) loc
      9  from
     10    test,
     11    table(cast(multiset(select level
     12                        from dual
     13                        connect by level <= regexp_count(location, ',') + 1
     14                       )
     15                       as sys.odcinumberlist
     16               ));
    
            ID ID2 LOC
    ---------- --- ----------------------------------------
             1 k12 CA
             1 k12 WA
             1 k12 NY
             2 k8  VA
             2 k8  TX
    
    SQL>

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    "append query" is MS Access terminology for an INSERT statement. At least it was back in 97. With any luck, they have updated some of their terms.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you; I presumed that it is not pure Oracle (I've been dealing with it for quite a while now and I've never heard of such a term).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •