CSV to Rows

Uli Bethke SQL for Analysis

I recently needed to convert a comma separated list of values in a table column to a column where each value in the list gets its own row in a table.

So first of all let's create such a table. I have limited the number of comma separated values that will be created to 12.

View Listing 1

To convert our csv list to rows we will create an in-memory helper table that for the maximum number of comma separated values has a corresponding number of rows. We can then use this helper table to join to our main table and then parse the values.

The SQL below will create our helper table:

View Listing 2

Results for the helper table will look like:

Now it is simply a matter of joining our main table to this helper table and parsing the results:

View Listing 3

In order to improve performance we could create a function based index for our join column.

There are other solutions that will do the same thing and requires less code, but will not perform as well. So if you have a small set of rows to convert you can use this solution: How do I split comma delimited data in a SQL Statement

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.