ID and List to rows

I had an interesting situation today needing to transform two columns – a user id and a list of roles – in rows, each containing a user id and one element from the list (one role id).

Of course, I could have made my own custom function using INSTR and SUBSTR but I wanted to use a simpler solution. This is what I came up with, using XMLTABLE:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 15 18:48:04 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

SQL> create table users_load (id integer, roles varchar2(100));

Table created.

SQL> insert into users_load values(1,'role1,role2,role3');

1 row created.

SQL> insert into users_load values(2,'role1');

1 row created.

SQL> select * from users_load;

ID         ROLES
---------- -------------------------------------------
1          role1,role2,role3
2          role1

SQL> select id, role
2      from users_load,
3           xmltable('r/c' passing
4                    xmltype('<r><c>' || replace(roles, ',', '</c><c>') ||
5                            '</c></r>') columns role varchar2(100) path '.');

ID         ROLE
---------- -------------------------------------------
1          role1
1          role2
1          role3
2          role1