Oracle SQL：在多个列/字段上透视
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38696067/
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 SQL: Pivot on multiple columns/fields
提问by Chris7b
I want to create a table where the entries of various columns are 'pivoted' to column headers. The table is for reporting purposes - my users want to query data via Excel (using Microsoft Query), and the problem is that doing the pivoting in Excel makes the files unpractically large and slow even for moderately-sized data sets (~100k data points).
我想创建一个表，其中各个列的条目“旋转”到列标题。该表用于报告目的 - 我的用户希望通过 Excel（使用 Microsoft Query）查询数据，问题是在 Excel 中进行透视会使文件变得不切实际，即使对于中等大小的数据集（~100k 数据点） ）。
Consider the following example:
考虑以下示例：
CREATE TABLE tt
(
"COMMODITY" VARCHAR2(4000 BYTE),
"MARKET" VARCHAR2(4000 BYTE),
"BID_ASK" VARCHAR2(4000 BYTE),
"PRICE" NUMBER
);
INSERT INTO tt VALUES ('Gold','US','Ask',1.1);
INSERT INTO tt VALUES ('Gold','US','Bid',1);
INSERT INTO tt VALUES ('Gold','EU','Ask',1.2);
INSERT INTO tt VALUES ('Gold','EU','Bid',1.1);
INSERT INTO tt VALUES ('Oil','US','Ask',11);
INSERT INTO tt VALUES ('Oil','US','Bid',10);
INSERT INTO tt VALUES ('Oil','EU','Ask',12);
INSERT INTO tt VALUES ('Oil','EU','Bid',11);
The output that I want to achieve would be something like (the exact column headers don't matter much):
我想要实现的输出类似于（确切的列标题并不重要）：
COMMODITY 'US_Bid' 'US_Ask' 'EU_Bid' 'EU_Ask'
Gold 1 1.1 1.1 1.2
Oil 10 11 11 12
Now it is straightforward to pivot a single column:
现在可以直接旋转单个列：
SELECT * FROM
(
SELECT * FROM tt
)
PIVOT
(
SUM(PRICE)
FOR MARKET IN ('US','EU')
)
Which gives:
这使：
COMMODITY BID_ASK 'US' 'EU'
Gold Bid 1 1.1
Oil Bid 10 11
Oil Ask 11 12
Gold Ask 1.1 1.2
According to my research there is no syntax for directly pivoting multiple columns. There are some related questions (here, hereor here), but I could not find a direct answer to my problem there. So I came up with the following solution:
根据我的研究，没有直接旋转多列的语法。有一些相关的问题（这里，这里或这里），但我在那里找不到我的问题的直接答案。所以我想出了以下解决方案：
SELECT * FROM
(
SELECT COMMODITY, CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK, PRICE FROM tt
)
PIVOT
(
SUM(PRICE)
FOR MARKET_BID_ASK IN ('US_Bid','US_Ask','EU_Bid','EU_Ask')
)
This produces exactly the desired output. However, I do not consider it a practical solution as the number of variables that I have to enter grows way too fast (in my real data set, I want to pivot more fields at once, all of which have many different values). I know that there exist dynamic pivots, however I am not sure whether this will work with Excel, and I would also like to keep the syntax as simple as possible, because the users will define queries on their own (I just want to provide a template query that they can adapt). So I tried to query the field names in the IN-clause:
这恰好产生了所需的输出。但是，我不认为这是一个实用的解决方案，因为我必须输入的变量数量增长得太快（在我的真实数据集中，我想一次旋转更多字段，所有字段都有许多不同的值）。我知道存在dynamic pivots，但是我不确定这是否适用于 Excel，而且我还希望使语法尽可能简单，因为用户将自行定义查询（我只想提供一个他们可以适应的模板查询）。所以我尝试查询 IN 子句中的字段名称：
SELECT * FROM
(
SELECT COMMODITY, CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK, PRICE FROM tt
)
PIVOT
(
SUM(PRICE)
FOR MARKET_BID_ASK IN
(
SELECT DISTINCT CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK FROM tt
)
)
I think that such a solution could be practical, because one could still constrain the variables queried without having to list all the concatenated options using LIKE-conditions in the subquery. However, I get an "ORA-00936 - missing expression" error with this query, even though subqueries should be legal here according to the documentationthat I found.
我认为这样的解决方案可能是实用的，因为仍然可以约束查询的变量，而不必在子查询中使用 LIKE 条件列出所有连接选项。但是，即使根据我找到的文档，子查询在这里应该是合法的，但我在此查询中收到“ORA-00936 - 缺少表达式”错误。
回答by Alex Poole
You can pivot on multiple columns by enclosing the columns, and the sets of values, in parentheses:
您可以通过将列和值集括在括号中来对多列进行透视：
SELECT * FROM
(
SELECT * FROM tt
)
PIVOT
(
SUM(PRICE)
FOR (MARKET, BID_ASK)
IN (('US', 'Bid') us_bid, ('US', 'Ask') us_ask, ('EU', 'Bid') eu_bid, ('EU', 'Ask') eu_ask)
);
COMMODITY US_BID US_ASK EU_BID EU_ASK
---------- ---------- ---------- ---------- ----------
Gold 1 1.1 1.1 1.2
Oil 10 11 11 12
but the value pairs still have to be known when the query is parsed, and this doesn't scale well if you have a lot of combinations of values.
但是在解析查询时仍然必须知道值对，如果你有很多值的组合，这不能很好地扩展。
Your only alternative is dynamic SQL, as you suspected, unless you can get Excel to process the result of an XML pivot - which I don't think is possible. With dynamic SQL you could perhaps have a function that does the query and pivot and returns a ref cursor, if Excel finds that easier to handle than the pivot query.
正如您所怀疑的，您唯一的选择是动态 SQL，除非您可以让 Excel 处理 XML 数据透视的结果 - 我认为这是不可能的。使用动态 SQL，如果 Excel 发现比数据透视查询更容易处理，您可能会拥有一个执行查询和数据透视并返回引用游标的函数。
