Unpivot, Split Column, Pivot Bigquery/Postgresql-CodePudding

I have table like this:

Old Table

Then I want to change the table to this:

New Table

I usually doing this in Power BI Query Editor but the file is too big so I try in another platform like Bigquery/Postgresql

Thank you

CodePudding user response：

You can cross join to a a values clause:

select t.fruits,
       u.*
from the_table t
  cross join lateral (
     values ('Store A', t.qty_store_a, t.value_store_a), 
            ('Store B', t.qty_store_b, t.value_store_b), 
            ('Store C', t.qty_store_c, t.value_store_c),
            ('Store BC', t.qty_store_bc, t.value_store_bc),
            ('Store DC', t.qty_store_dc, t.value_store_dc)
  ) as u(store, qty, value)

CodePudding user response：

Consider below solution (for BigQuery)

select Fruits, regexp_extract(Store, r'Qty_(.*?)_Value') Store, Qty, Value
from your_table
unpivot (
  (Qty, Value) for 
  Store in (
    (Qty_Store_A, Value_Store_A), 
    (Qty_Store_B_C, Value_Store_B_C), 
    (Qty_Store_D_C, Value_Store_D_C)
  )
)

If applied to sample data in your question - output is

And also, slightly modified version of above where you can define Store Names explicitly if for some reason RegEx way does not work for you

select Fruits, Store, Qty, Value
from your_table
unpivot (
  (Qty, Value) for 
  Store in (
    (Qty_Store_A, Value_Store_A) as 'Store_A', 
    (Qty_Store_B_C, Value_Store_B_C) as 'Store_B_C', 
    (Qty_Store_D_C, Value_Store_D_C) as 'Store_D_C'
  )
)

Obviously, with same output