在BigQuery中将列转置为行

Question

对于BigQuery，如何转置列'Ds Hrc Shm'，'Ds Orc Cwp‘...将“sku”中的行转换为列，并使其中的值保持相同。

从…

   SKU     | Ds Hrc Shm  | Ds Orc Cwp  | Ds Orc Mwp  
DB-MIX-HTS        1             1              1
DB-ORC-TUS        0             1              1

转到

          DB-MIX-HTS | DB-ORC-TUS 
Ds Hrc Shm      1           0
Ds Orc Cwp      1           1
Ds Orc Mwp      1           1

我应该使用pivot / unvipot还是任何简单的方法？

我尝试使用pivot，但无法在IN中声明列标题

SELECT *
FROM
(
SELECT `manual_input.sku_translations`.`ds_hrc_shm` AS `ds_hrc_shm`, 
`manual_input.sku_translations`.`sku` AS `sku_1`,
 FROM `manual_input.sku_translations`
 ) 
 PIVOT
(
 SUM(`ds_hrc_shm`) FOR `sku_1` IN (['DB-ORC-TWS'], ['DB-MIX-SGT'])
 ) 

Here's the sample of data

Answer 1

考虑下面的方法

select * from (
  select * replace(replace(SKU, '-', '_') as SKU)
  from `manual_input.sku_translations`
  unpivot (value for col in (`Ds Hrc Shm`, `Ds Orc Cwp`, `Ds Orc Mwp`))
)
pivot (any_value(value) for SKU in ('DB_MIX_HTS', 'DB_ORC_TUS'))    

如果要应用于问题中的样本数据，则

with `manual_input.sku_translations` as (
  select 'DB-MIX-HTS' SKU, 1 as `Ds Hrc Shm`, 1 as `Ds Orc Cwp`, 1 as `Ds Orc Mwp` union all
  select 'DB-ORC-TUS', 0, 1, 1
)           

输出为

​

​