我正在尝试在 Oracle 中使用 PIVOT，但结果很奇怪.这可能只是我需要设置的一个选项，但我对 Oracle/SQL 的了解可以放入此评论框中.

I'm trying to use PIVOT in Oracle and I'm getting a weird result. It's probably just an option I need to set but what I know about Oracle/SQL I could fit into this comment box.

这是我的查询示例:

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','John','Jane')

结果与我预期的一样，除了列名周围有单引号(来自 Toad 的图片 - 如果我导出到 Excel，引号会被带到 Excel):

The results come out as I expected except the Column names have single quotes around them (picture from Toad - if I export to Excel the quotes get carried to Excel):

如何去掉列名周围的单引号?我尝试在in"子句中取出它们，但出现错误:

How do I get rid of the single quotes around the column names? I tried taking them out in the "in" clause and I get an error:

in (Fred,John,Jane)

我也尝试用双引号替换单引号并得到同样的错误.我不知道这是否是我需要在运行查询或 Toad 之前设置/取消设置的 Oracle 选项.

I also tried replacing the single quotes with double quotes and got the same error. I don't know if this is an Oracle option I need to set/unset before running my query or a Toad thing.

推荐答案

您可以为 pivot 语句的 IN 子句中的新列提供别名.(注意:这与不允许别名的标准 where 子句 IN() 不同.)

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

返回

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

当然，您可以转一圈并使用标准的 oracle 别名并将它们重命名为您喜欢的任何名称，包括再次将引号放回..

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

给予

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