我有一个问题:
select * from (
select
ab_id, abc_metric, abc_value from TABLE1)
PIVOT(
min(abc_value)
FOR abc_metric IN ('Catalog','Fruit Type')); -- I have 50 such columns
这将生成一个表:
ab_id 'Catalog' 'Fruit Type'
1 a b
2 c d
我希望表中的列不带引号,并用_
分隔
ab_id catalog fruit_type
1 a b
2 c d
有没有办法做到这一点?
发布于 2019-07-22 00:25:50
您可以简单地在PIVOT
中添加AS
和别名
select *
from (select ab_id, abc_metric, abc_value from TABLE1)
PIVOT(min(abc_value)
FOR abc_metric IN ('Catalog' AS Catalog,'Fruit Type' AS Fruit_type)
);
发布于 2019-07-22 00:26:15
只需使用条件聚合:
select ab_id,
max(case when abc_metric = 'Catalog' then abc_value end) as catalog,
max(case when abc_metric = 'Fruit Type' then abc_value end) as fruit_type
from TABLE1
group by ab_id
https://stackoverflow.com/questions/57134987
复制相似问题