8

For example, I have the following table:

| Block                       |
| abcdefgh,12kjkjkj,231wewoxyz|

How can I convert it into:

| Block1   | Block2   | Block3     |
| abcdefgh | 12kjkjkj | 231wewoxyz |

Note:

  • Each "Block" has a maximum of 8 commas (and hence can be split into 9 smaller blocks). The number of commas varies; some have 8 commas, some have 4, some have 7, etc. Each smaller block will occupy a column.

I use Presto, btw.

dbc
  • 104,963
  • 20
  • 228
  • 340
Tai Ngo
  • 97
  • 1
  • 1
  • 5
  • 1
    What database are you using? – Alex Howansky Sep 13 '18 at 17:03
  • I use Presto @AlexHowansky – Tai Ngo Sep 13 '18 at 17:06
  • Presto has a built-in split function: https://prestodb.io/docs/current/functions/string.html – Alex Howansky Sep 13 '18 at 17:08
  • @AlexHowansky thanks; that helps me with the first step. For the second step, I'm trying to split the strings into different columns. – Tai Ngo Sep 13 '18 at 17:13
  • What is the ultimate goal? What are you starting with and where would you like to end up? – Shawn Sep 13 '18 at 17:19
  • And if you have some lists with 4 commas, and others with 8, how do you decide which columns they go into? – Shawn Sep 13 '18 at 17:20
  • It is like tagging. For example, a product (Nike Shoes) has tags such as "running", "marathon", "nikeair". In the database, they are stored as one single value, "running,marathon,nikeair", so when I query, I want to be able to store in a single row running | marathon | nikeair. Each value will have 9 columns. Some will have 9 columns with value, some with empty columns. @Shawn – Tai Ngo Sep 13 '18 at 17:26
  • Storing multiple, delimited (denormalized) values in a single column is a very bad way to design what you're talking about. You are just setting yourself up for a lot of trouble down the road. I would strongly urge you to reconsider this design decision. – Shawn Sep 13 '18 at 17:58
  • @Shawn, I'm not designing any database. The existing database has a column which stores multiple, delimited values. I'm just trying to extract the relevant information inside. – Tai Ngo Sep 13 '18 at 18:01
  • You want to turn the comma-delimited string into a row of columns? How do you plan to use those columns? Even if you aren't planning to create a new table for those values, it would probably be easier to consume those columns in SQL if you unpivoted that data and used it as a single column and accessed the data in the row representing that column. Then if you want to find out all items that are tagged as "running", you won't have to look at each type "shoe" and filter through `Column1` through `Column8` to find out if any of them are "running". – Shawn Sep 13 '18 at 18:36

1 Answers1

17

Seems to me you would simply use split_part function to create your columns, like so:

SELECT
    split_part(Block, ',', 1) AS Block1,
    split_part(Block, ',', 2) AS Block2,
    split_part(Block, ',', 3) AS Block3,
    split_part(Block, ',', 4) AS Block4,
    split_part(Block, ',', 5) AS Block5,
    split_part(Block, ',', 6) AS Block6,
    split_part(Block, ',', 7) AS Block7,
    split_part(Block, ',', 8) AS Block8,
    split_part(Block, ',', 9) AS Block9
FROM
    your_table
Nick
  • 7,103
  • 2
  • 21
  • 43