Pivot/Unpivot in Bigquery-CodePudding

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 ?

CodePudding user response：

1. Consier below query:

SELECT * FROM (
  SELECT * EXCEPT(txt) FROM sample
) PIVOT (ANY_VALUE(prod_hier) FOR hier_lvl IN ('prodh1', 'prodh2', 'prodh3', 'prodh4', 'prodh5', 'prodh6', 'prodh7'))
JOIN (
  SELECT * EXCEPT(prod_hier) FROM sample
) PIVOT (ANY_VALUE(txt) FOR hier_lvl IN ('prodh1', 'prodh2', 'prodh3', 'prodh4', 'prodh5', 'prodh6', 'prodh7'))
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:

CodePudding user response：

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