BigQuery中的枢轴/Undivot

发布于 2025-02-11 01:10:29 字数 1575 浏览 5 评论 0原文

我需要更改以下产品层次结构bigquery表的结构：应

当前

Salesorg	ister_chan	材料	hier_lvl	prod_hier	txt
ph01	20	680483	PRODH1	PH	菲律宾
PH01	20	680483	PODH2	PHGR	杂货店
杂货店	20	680483	PH01	杂货店	Mayo
20	使用	：	PHGRGR731Q27		Eden
BigQuery	表	SQL
		680483	prodh6	phgrgr731q27p410	sprave
80483	20	680483	prodh7	phgrgr731q27p410pi	瓶

需要

salessorg	istresor_chan	材料	prodh2	prodh3	prodh3	PHILIPPINE	prodh4	PHILIPPINE	桌	销售	prodh5	Sandwich	1	PHGR	prodh5	PHGRGR
PHGR731	表	PHGRGR731Q27P410PI				PHGRGR731Q27		PHGRGR731Q27P410

有办法实现这一目标吗？

原文

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 ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酒几许 2025-02-18 01:10:29

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);

。 //i.sstatic.net/qsv20.png“ alt =”在此处输入图像描述”>

2。（另一个选项）（另一个选项）与您的删除输出不完全相同，但可以是一个选项。

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'));

输出：

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:

回复收藏 0 原文

无远思近则忧 2025-02-18 01:10:29

请使用以下方法

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)
)

如果应用于您的问题中的示例数据，

- 输出为

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