Question 114352 in PostgreSQL Question posted in
The official documentation can be found here.

Unpivot, Split Column, Pivot Bigquery/Postgresql

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    enter image description here

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search