Oracle SQL Developer：如何使用 PIVOT 函数将行转换为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29945504/
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 Developer: How to transpose rows to columns using PIVOT function
提问by user1309226
I'm attempting to create a query to transpose rows into columns using the PIVOT function.
我正在尝试创建一个查询以使用 PIVOT 函数将行转换为列。
This is the contacttable I want to transpose into rows:
这是contact我想转换成行的表：
PARTYID CONTACTTEXT CONTACTTYPECD
---------- ------------ -------------
100 0354441010 1
100 0355551010 2
100 0428105789 3
100 [email protected] 4
My intended result:
我的预期结果：
PARTYID PHONE FAX MOBILE EMAIL
---------- ------------ ------------ ------------ ------------
100 0354441010 0355551010 0428105789 [email protected]
My query:
我的查询：
SELECT * FROM
(
SELECT partyId, contacttext, contacttypecd
FROM CONTACT
WHERE partyId = 100;
)
PIVOT (
MAX(contacttext)
FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email));
Errors I'm getting:
我得到的错误：
Error starting at line 9 in command:
FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email))
Error report:
Unknown Command
The reason for my problem was because my Oracle database version (Oracle9i) did not support the PIVOT function. Here's how to do it in a different way:
我的问题的原因是因为我的Oracle数据库版本（Oracle9i）不支持PIVOT功能。以下是如何以不同的方式做到这一点：
SELECT PartyCD
,MAX(DECODE(t.contacttypecd, 1, t.contacttext)) Phone
,MAX(DECODE(t.contacttypecd, 2, t.contacttext)) Fax
,MAX(DECODE(t.contacttypecd, 3, t.contacttext)) Mobile
,MAX(DECODE(t.contacttypecd, 4, t.contacttext)) Email
FROM
(
SELECT partyid, contacttext, contacttypecd
FROM CONTACT
WHERE partyid = 100
) t
GROUP BY PartyID
回答by Alex Poole
You have a stray semi-colon in your statement, after:
您的语句中有一个杂散的分号，后面是：
WHERE partyId = 100;
Remove that to make it:
删除它以使其：
SELECT * FROM
(
SELECT partyId, contacttext, contacttypecd
FROM CONTACT
WHERE partyId = 100
)
PIVOT (
MAX(contacttext)
FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email));
PARTYID PHONE FAX MOBILE EMAIL
---------- ------------ ------------ ------------ ------------
100 0354441010 0355551010 0428105789 [email protected]
It's being seen as multiple statements; the first is incomplete because it's missing a closing parenthesis (so gets ORA-00907), the second starts with that parenthesis and gets the error you reported, and then each subsequent line gets the same error. You only seem to be looking at the last reported error - it's usually much more helpful to start with the first error, clear that, and then move onto the next if it still exists.
它被视为多重陈述；第一个不完整，因为它缺少一个右括号（所以得到 ORA-00907），第二个以该括号开头并得到您报告的错误，然后每个后续行都得到相同的错误。您似乎只查看上次报告的错误 - 从第一个错误开始，清除它，然后如果它仍然存在，则转到下一个错误通常更有帮助。
