CODEWITHSUNDEEP
sqlgoogle-bigquery
Maykid
Maykid

Reputation: 517

How to autofill NULL values when using SPLIT formula in SQL?

So I'm trying to figure out how I would go about using the SPLIT function in Big Query SQL while filling in the NULL values that associate with when using the SPLIT function. For this example I have two columns in my table currently and looks like this:

    Name             |           Price
 Jake                |       19.99,20.00,21.00
 Jane                |       15.00,13.99,14.00
 Joe                 |       17.99,12.00,15.00,17.99
 John                |       18.00,10.00

The output I'm getting currently when I do, SPLIT(Price, ',') AS Price_Split is the following:

    Name             |           Price
 Jake                |       19.99
                     |       20.00
                     |       21.00
 Jane                |       15.00
                     |       13.99
                     |       14.00
 Joe                 |       17.99
                     |       12.00
                     |       15.00
                     |       17.99
 John                |       18.00
                     |       10.00

What I'm trying to make my end result look like is:

    Name             |           Price
 Jake                |       19.99
 Jake                |       20.00
 Jake                |       21.00
 Jane                |       15.00
 Jane                |       13.99
 Jane                |       14.00
 Joe                 |       17.99
 Joe                 |       12.00
 Joe                 |       15.00
 Joe                 |       17.99
 John                |       18.00
 John                |       10.00

How would I go about auto filling in the Names that correspond correctly? Or should I go about this a different way/different formula? Any help with this would be much appreciated!!

Upvotes: 0

Views: 241

Answers (1)

Pentium10
Pentium10

Reputation: 207912

You need to UNNEST

WITH data AS(
  SELECT 'Joe' as name, SPLIT('17.99,12.00,15.00,17.99', ',') AS r 
)
SELECT
name,r
FROM data,
UNNEST(r) r


"name","r"
"Joe","17.99"
"Joe","12.00"
"Joe","15.00"
"Joe","17.99"

Upvotes: 2

Related Questions