Splitting one row into two rows based on IDs

Question

I am querying a data base that has two ID's separated by commas in a single Column- ID

table : user

B_ID   FirstName   LastName  email
B5,B6  Mo         Asif     xxx
B1     Adam      chung     xxx

As Mo has two ID's: B5, B6 - how can I query the data base so I can split them into two separate rows, - Exactly like this

B_ID   FirstName   LastName  email
B5      Mo         Asif     xxx
B6      Mo         Asif     xxx
B1      Adam       chung    xxx

There are cases when there are 3 ID's, but I want same results for 3,4..IDs

I able to split one them into two different Columns using SUBSTR function, but that doesn't fit my purpose. Don't know how would I able to split them to rows — Asif, Sep 15 '15 at 16:55
ID's can be up to 3 separated by commas. ID symbols length vary, it's not constant - can go upto 10 symbols — Asif, Sep 15 '15 at 17:06

score 1 · Answer 1 · edited May 23 '17 at 12:29

This handles as many B_IDs as there are and NULL B_ID elements. Always test for unexpected values/conditions and make sure you are handling them! I suggest renaming that B_ID column. The name implies a unique identifier which it obviously is not. Either that or some further normalization is required.

Note the regular expression which handles NULL list elements. The commonly used expression of '[^,]+' for parsing lists does not handle NULL elements.

SQL> with tbl(B_ID, FirstName, LastName, email) as (
     select 'B5,B6',  'Mo',    'Asif',  'xxx@yxz.com' from dual
     union
     select 'B1',     'Adam',  'chung', 'xxx@xyz.com' from dual
     union
     select 'B7,,B9', 'Lance', 'Link',  'llink@ape.org'     from dual
     union
     select '',       'Mata',  'Hari',  'mhari@ape.org'     from dual
   )
    SELECT REGEXP_SUBSTR(B_ID , '(.*?)(,|$)', 1, COLUMN_VALUE, NULL, 1 ) AS B_ID,
          firstname, lastname, email
    FROM   tbl,
          TABLE(
            CAST(
              MULTISET(
                SELECT LEVEL
                FROM   DUAL
                  CONNECT BY LEVEL <= REGEXP_COUNT(B_ID , ',' )+1
              ) AS SYS.ODCINUMBERLIST
            )
          );

B_ID   FIRST LASTN EMAIL
------ ----- ----- -----------
B1     Adam  chung xxx@xyz.com
B5     Mo    Asif  xxx@yxz.com
B6     Mo    Asif  xxx@yxz.com
B7     Lance Link  llink@ape.org
       Lance Link  llink@ape.org
B9     Lance Link  llink@ape.org
       Mata  Hari  mhari@ape.org

7 rows selected.

SQL>

I actually got rid of all Null values using `B_ID is not null` but its still useful if I have to deal with a case where there are null list elements I need to deal with — Asif, Sep 16 '15 at 12:05

score 0 · Accepted Answer · answered Sep 15 '15 at 17:25

WITH cte AS
      (SELECT B_ID,FirstName,LastName,email  FROM t)
SELECT  REGEXP_SUBSTR(t1.B_ID, '([^,])+', 1, t2.COLUMN_VALUE),FirstName,LastName,email
FROM cte t1 CROSS JOIN
            TABLE
            (
                CAST
                (
                    MULTISET
                    (
                        SELECT LEVEL
                        FROM DUAL 
                        CONNECT BY LEVEL <= REGEXP_COUNT(t1.B_ID, '([^,])+')
                    )
                    AS SYS.odciNumberList
                )
            ) t2;

FIDDLE

I am trying to understand how you worked out this query - I can see following steps - Created a table called cte and dumped the select data from table t - Select the substring before a comma from the series generated table having counts of all the strings and called it t2 — Asif, Sep 16 '15 at 11:59

score 0 · Answer 3 · answered Sep 15 '15 at 18:55

0

Simple Union, substr and insert should solve your problem.

mysql> select substr('B5,B6',instr('B5,B6',',')-2,2) as a from dual
    -> UNION
    -> select substr('B5,B6',instr('B5,B6',',')+1,2) as a from dual;

output:

+----+
| a  |
+----+
| B5 |
| B6 |
+----+
2 rows in set (0.06 sec)

Hope it helps you :)

answered Sep 15 '15 at 18:55

Nitin Tripathi

1,224
7
17

I'm afraid this does not allow for the OP's requirement of handling a variable number of B_IDs. – Gary_W Sep 15 '15 at 21:09

Splitting one row into two rows based on IDs

3 Answers3