Oracle Pivot查询为列提供了带引号的列名.什么? [英] Oracle Pivot query gives columns with quotes around the column names. What?

问题描述

我试图在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)

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

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

这篇关于Oracle Pivot查询为列提供了带引号的列名.什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！