SQL Oracle Pivot 查询在列名周围提供带引号的列。什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22103060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Pivot query gives columns with quotes around the column names. What?
提问by Richard Schaefer
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.
我正在尝试在 Oracle 中使用 PIVOT，但得到了一个奇怪的结果。这可能只是我需要设置的一个选项，但我对 Oracle/SQL 的了解可以放入此评论框中。
Here's an example of my query:
这是我的查询示例：
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')
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):
结果如我所料，除了列名称周围有单引号（来自 Toad 的图片 - 如果我导出到 Excel，引号将被传送到 Excel）：
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”子句中取出它们，但出现错误：
in (Fred,John,Jane)
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.
我也尝试用双引号替换单引号并得到相同的错误。我不知道这是否是我需要在运行查询或 Toad 之前设置/取消设置的 Oracle 选项。
回答by ShoeLace
you can provide aliases to the new columns in the pivotstatement's INclause.
(NB: This is different from the standard where clause IN()which does not allow aliases.)
您可以为pivot语句IN子句中的新列提供别名。（注意：这与IN()不允许别名的标准 where 子句不同。）
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..
当然，然后您可以完整循环并使用标准的 oracle 别名并将它们重命名为您喜欢的任何名称，包括再次将引号放回原处。
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
