Pivot/Unpivot in Bigquery

Question

I have a requirement to change the structure of Product Hierarchy Bigquery table as below: This should be done using Bigquery SQL

Current Table:

salesorg	distr_chan	material	hier_lvl	prod_hier	txt
PH01	20	680483	prodh1	PH	Philippines
PH01	20	680483	prodh2	PHGR	Grocery
PH01	20	680483	prodh3	PHGRGR	Grocery
PH01	20	680483	prodh4	PHGRGR731	Eden Mayo
PH01	20	680483	prodh5	PHGRGR731Q27	Eden Mayo
PH01	20	680483	prodh6	PHGRGR731Q27P410	Sandwich Spread
PH01	20	680483	prodh7	PHGRGR731Q27P410PI	Bottle

Required Table

salesorg	distr_chan	material	prodh1	prodh2	prodh3	prodh4	prodh5	prodh6	prodh7	prodh1txt	prodh2txt	prodh3txt	prodh4txt	prodh5txt	prodh6txt	prodh7txt
PH01	20	680483	PH	PHGR	PHGRGR	PHGRGR731	PHGRGR731Q27	PHGRGR731Q27P410	PHGRGR731Q27P410PI	Philippines	Grocery	Grocery	Eden Mayo	Eden Mayo	Sandwich Spread	Bottle

Is there a way to achieve this ?

Does this help? https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery — JonTout, Jun 28 '22 at 11:35
I dont think so, as i dont want to aggregate any column. Also, i want prodh1, prodh2....prodh7 as the new column names instead of creating column1 and putting value as prodh1. Hope I am able to explain the requirement here. — Vikas Tiwari, Jun 28 '22 at 11:47

Jaytiger · Answer 1 · 2022-06-29T12:39:36.393

1. Consier below query:

SELECT h.*,
       txt_prodh1 AS prodh1txt,
       txt_prodh2 AS prodh2txt,
       txt_prodh3 AS prodh3txt,
       txt_prodh4 AS prodh4txt,
       txt_prodh5 AS prodh5txt,
       txt_prodh6 AS prodh6txt,
       txt_prodh7 AS prodh7txt,
  FROM (
    SELECT * EXCEPT(txt) FROM sample
  ) PIVOT (ANY_VALUE(prod_hier) FOR hier_lvl IN ('prodh1', 'prodh2', 'prodh3', 'prodh4', 'prodh5', 'prodh6', 'prodh7')) AS h
  JOIN (
    SELECT * EXCEPT(prod_hier) FROM sample
  ) PIVOT (ANY_VALUE(txt) txt FOR hier_lvl IN ('prodh1', 'prodh2', 'prodh3', 'prodh4', 'prodh5', 'prodh6', 'prodh7')) AS t
  USING (salesorg, distr_chan, material);

output will be:

2. (another option) Not exactly same as your exptected output, but can be an option.

SELECT * FROM (
  SELECT * EXCEPT(prod_hier, txt), STRUCT(prod_hier AS hier, txt) AS prod FROM sample
) PIVOT (ANY_VALUE(prod) FOR hier_lvl IN ('prodh1', 'prodh2', 'prodh3', 'prodh4', 'prodh5', 'prodh6', 'prodh7'));

output:

Thanks a lot. I am going with approach 1 and getting the desired output. Any idea of how can i change the column name for the second set of columns ? — Vikas Tiwari, Jun 29 '22 at 11:23
@VikasTiwari, I've updated the query to change column name. kindly check that out — Jaytiger, Jun 29 '22 at 12:40

score 0 · Answer 2 · answered Jun 28 '22 at 23:16

0

Use below approach

select * from your_table
pivot (
  any_value(prod_hier) prodh, any_value(txt) prodhtxt 
  for cast(replace(hier_lvl, 'prodh', '') as int64) in (1,2,3,4,5,6,7)
)

if applied to sample data in your question - output is

answered Jun 28 '22 at 23:16

Mikhail Berlyant

165,386
8
154
230

Thank you, but the casting for hier_level is not happening and i get an error as: Bad int64 value: hier1 – Vikas Tiwari Jun 29 '22 at 11:24
per data sample in your question - you have `prodh1`, `prodh2`, ... `prodh7` as a values for `hier_lvl` column. Looks like your real values are different! so just adjust replace statement respectively ! – Mikhail Berlyant Jun 29 '22 at 13:27

Pivot/Unpivot in Bigquery

2 Answers2