CODEWITHSUNDEEP
google-bigquery
ErrHuman
ErrHuman

Reputation: 345

Trying to split cells in multiple columns into rows - UNNEST query

Good day all,

I've found solutions on the site for similar problems with a single column, which helped me halfway through, but I'm stuck at the final part when I need to unnest the array.

Here is the query so far:

#StandardSQL
With Data AS
(
SELECT 'aaa' as Item, 'EA/BX/PA' as UOM, '1/10/100' as Factor UNION ALL
SELECT 'bbb' as Item, 'EA/PA' as UOM, '1/50' as Factor UNION ALL
SELECT 'ccc' as Item, null as UOM, null as Factor 
),
SplitData AS
(
SELECT
Item,
SPLIT(UOM, "/") as UOM,
SPLIT(Factor, "/") as Factor
FROM Data
)
SELECT
*
FROM
SplitData,
UNNEST(SplitData.UOM),UNNEST(SplitData.Factor)

The result is:

--Row---+--Item---+--UOM--¦--Factor--+--f0_--+--f1_--
   1    ¦  aaa    ¦  EA   ¦    1     ¦  EA   ¦  1
        ¦         ¦  BX   ¦   10     ¦       ¦
        ¦         ¦  PA   ¦  100     ¦       ¦
--------+---------+-------+----------+-------+-------    
   2    ¦  aaa    ¦  EA   ¦    1     ¦  EA   ¦  10
        ¦         ¦  BX   ¦   10     ¦       ¦
        ¦         ¦  PA   ¦  100     ¦       ¦
--------+---------+-------+----------+-------+-------

etc, etc

What I want to see is this:

--Row---+--Item---+--UOM--¦--Factor--+
   1    ¦  aaa    ¦  EA   ¦    1     ¦
--------+---------+-------+----------+
   2    ¦  aaa    ¦  BX   ¦   10     ¦
--------+---------+-------+----------+
   3    ¦  aaa    ¦  PA   ¦  100     ¦
--------+---------+-------+----------+
   4    ¦  bbb    ¦  EA   ¦    1     ¦ 
--------+---------+-------+----------+

etc, etc

I get that the problem is the double unnest in my query which is trying to join each with each, but when I select the ItemCode only, I'm not getting the UOMs and Factors at all...

Any idea how I need to amend the query to get this result, please?

Upvotes: 0

Views: 1841

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL and assumes (based on the example of sample data) that number of elements in UOM and Factor is the same 

#standardSQL
SELECT item, u AS UOM, f AS Factor
FROM `project.dataset.data`
LEFT JOIN UNNEST(SPLIT(UOM, '/')) u WITH OFFSET
JOIN UNNEST(SPLIT(Factor, '/')) f WITH OFFSET
USING(OFFSET)

If to apply to sample data from your question - result is 

Row item    UOM     Factor   
1   aaa     EA      1    
2   aaa     BX      10   
3   aaa     PA      100  
4   bbb     EA      1    
5   bbb     PA      50

Upvotes: 1

Y.K.
Y.K.

Reputation: 692

try this:

With

Data AS (
    SELECT 'aaa' as Item, 'EA/BX/PA' as UOM, '1/10/100' as Factor UNION ALL
    SELECT 'bbb' as Item, 'EA/PA' as UOM, '1/50' as Factor UNION ALL
    SELECT 'ccc' as Item, null as UOM, null as Factor 
),

SplitData AS (
    SELECT
        Item,
        SPLIT(UOM, "/") as UOM,
        SPLIT(Factor, "/") as Factor
    FROM
        Data
)

SELECT
    *,
    Factor[offset(uom_offset)] as unnested_factor
FROM
    SplitData as sd
    cross join UNNEST(sd.UOM) as unnested_uom  -- use left join if you want to display 'ccc' Item
        with offset as uom_offset

one more:

With

Data AS (
    SELECT 'aaa' as Item, 'EA/BX/PA' as UOM, '1/10/100' as Factor UNION ALL
    SELECT 'bbb' as Item, 'EA/PA' as UOM, '1/50' as Factor UNION ALL
    SELECT 'ccc' as Item, null as UOM, null as Factor
),

SplitData AS (
    SELECT
        Item,
        SPLIT(UOM, "/") as UOM,
        SPLIT(Factor, "/") as Factor
    FROM Data
)

SELECT
    sd.*,
    unnested_uom,
    unnested_factor
FROM
    SplitData as sd
    left join UNNEST(sd.UOM) as unnested_uom
        with offset as uom_offset
    left join UNNEST(sd.Factor) as unnested_factor
        with offset as factor_offset
where
    coalesce(uom_offset, factor_offset, 1)
    = coalesce(factor_offset, uom_offset, 1)

Upvotes: 1

Related Questions