I have a csv file with the below structure.
USer,Role
"A","p,q"
"B","p"
"C","q,r,s"
I am trying to write a sql query to parse the data to form the below output
user,role
A,P
A,q
B,p
C,q
C,r
C,s
I am trying to use REGEX but still unable to parse multi value columns into
multiple rows. ANy suggestions
|
A new feature will be available in 48 hours.
Split Multivalue Column Into Rows
If each row has multiple multi value columns, a special subquery table can be used in special join sql. For instance,
select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;
select User,Role,Year from aTable,(select split(aTable.Roles,',') as Role,split(aTable.Years,',') as Year) AS bTable;
|
Hello,
I tried using the query above.
Its not working please provide in details that will be parse comma separated values into multiple rows based on example provided above.
|
You should download the latest package.
select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;
can work.
|
Any other approach, rather than downloading the latest package.
can we use CONNECT BY to do such parsing.
|
Main
webmaster@hxtt.com