aliasing pivot column

Question

i am trying to alias the pivot column to scenario1, scenario2, scenario3 instead of 1,2,3. I am getting error.

select *
from (select *
      from (select s.campaign_id campaign_id, s.scenario_index scenario_index 
            from scenario s, campaign c where s.campaign_id = c.campaign_id)
      pivot (max(scenario_index)for scenario_index in (1,2,3))
     )a

thank you, aggregation gives the result with alias now. The requirement i have is to combine these columns with another query which is

select  CASE WHEN AWARD_TYPE = 0 THEN award_rate||' points'
                                        when AWARD_TYPE = 1   then Award_rate||' %'
                                        when award_type=2  then RATIO_POINTS||' points per '||RATIO_MON_UNIT||' AED' End
                            from  points_rule p
                            where c.pt_basic_rule_id = p.point_rule_id ) as pool_awards,

this query comes as a column and then the scenario1, 2,3 should come as 3 columns with the value of the pool_award based on the campaign_id

Why are you choosing not to use proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Jun 03 '20 at 10:57
You have several options to add aliases in pivot, described for example [here](https://stackoverflow.com/questions/22103060/oracle-pivot-query-gives-columns-with-quotes-around-the-column-names-what). — Ponder Stibbons, Jun 03 '20 at 11:12

score 0 · Answer 1 · answered Jun 03 '20 at 10:58

0

Just use conditional aggregation:

select s.campaign_id,
       max(case when scenario_index = 1 then 1 end) as scenario1,
       max(case when scenario_index = 2 then 1 end) as scenario2,
       max(case when scenario_index = 3 then 1 end) as scenario3
from scenario s join
     campaign c 
     on s.campaign_id = c.campaign_id
group by campaign_id;

answered Jun 03 '20 at 10:58

Gordon Linoff

1,242,037
58
646
786

thank you, it gives the same result with column alias, the requirement i have is to combine these columns with another query select CASE WHEN AWARD_TYPE = 0 THEN award_rate||' points' when AWARD_TYPE = 1 then Award_rate||' %' when award_type=2 then RATIO_POINTS||' points per '||RATIO_MON_UNIT||' AED' End from points_rule p where c.pt_basic_rule_id = p.point_rule_id ) as pool_awards – selva Jun 03 '20 at 11:20

score 0 · Answer 2 · answered Jun 03 '20 at 11:27

You can use an alias in IN clause of the PIVOT as follows:

select *
from (select *
      from (select s.campaign_id campaign_id, s.scenario_index scenario_index 
            from scenario s, campaign c where s.campaign_id = c.campaign_id)
      pivot (max(scenario_index)for scenario_index in (1 as scenario1,2 as scenario2,3 as scenario3))
     )a

aliasing pivot column

2 Answers2