Oracle Pivot Query Gives Columns with Quotes Around the Column Names. What

Oracle Pivot query gives columns with quotes around the column names. What?

you can provide aliases to the new columns in the pivot statement's IN clause.
(NB: This is different from the standard where clause IN() which does not allow aliases.)

with testdata as
(
    select 'Fred' First_Name, 10 Items from dual
    union
    select 'John' First_Name, 5  Items from dual
    union 
    select 'Jane' First_Name, 12 Items from dual
    union
    select 'Fred' First_Name, 15 Items from dual
)
select * from testdata
pivot (
      sum(Items) 
      for First_Name
      in ('Fred' as fred,'John' as john,'Jane' as jane)
      )

and also for your aggregate clause which is necessary if you have multiple clauses..

with testdata as
(
    select 'Fred' First_Name, 10 Items from dual
    union
    select 'John' First_Name, 5  Items from dual
    union 
    select 'Jane' First_Name, 12 Items from dual
    union
    select 'Fred' First_Name, 15 Items from dual
)
select * from testdata
pivot (
    sum(Items) itmsum,
    count(Items) itmcnt
    for First_Name
    in ('Fred' as fred,'John' as john,'Jane' as jane)
   )

returns

FRED_ITMSUM FRED_ITMCNT JOHN_ITMSUM JOHN_ITMCNT JANE_ITMSUM JANE_ITMCNT
----------- ----------- ----------- ----------- ----------- -----------
         25           2           5           1          12           1

Of course you can then go full circle and use standard oracle aliasing and rename them to whatever you like including putting quotes back in again..

with testdata as
(
    select 'Fred' First_Name, 10 Items from dual
    union
    select 'John' First_Name, 5  Items from dual
    union 
    select 'Jane' First_Name, 12 Items from dual
    union
    select 'Fred' First_Name, 15 Items from dual
)
select FRED_ITMSUM "Fred's Sum", FRED_ITMCNT "Fred's Count"
     , JOHN_ITMSUM "John's Sum", JOHN_ITMCNT "John's Count"
     , JANE_ITMSUM "Janes's Sum", JANE_ITMCNT "Janes's Count"
from testdata
pivot (
    sum(Items) itmsum,
    count(Items) itmcnt
    for First_Name
    in ('Fred' as fred,'John' as john,'Jane' as jane)
   )

gives

Fred's Sum Fred's Count John's Sum John's Count Janes's Sum Janes's Count
---------- ------------ ---------- ------------ ----------- -------------
        25            2          5            1          12             1

Oracle Pivot - Assign Column Name without quotes

You could simply add AS and alias inside PIVOT:

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

db<>fiddle demo

Dynamic Pivot in Oracle's SQL

You can't put a non constant string in the IN clause of the pivot clause.

You can use Pivot XML for that.

From documentation:

subquery A subquery is used only in conjunction with the XML keyword.
When you specify a subquery, all values found by the subquery are used
for pivoting

It should look like this:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;

You can also have a subquery instead of the ANY keyword:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;

Here is a sqlfiddle demo

---

Related Topics