Oracle SQL Developer:如何使用PIVOT函数将行转置为列 [英] Oracle SQL Developer: How to transpose rows to columns using PIVOT function

问题描述

我正在尝试创建一个查询，以使用PIVOT函数将行转置为列.

I'm attempting to create a query to transpose rows into columns using the PIVOT function.

这是我要转置为行的contact表:

This is the contact table I want to transpose into rows:

   PARTYID CONTACTTEXT  CONTACTTYPECD
---------- ------------ -------------
       100 0354441010               1
       100 0355551010               2
       100 0428105789               3
       100 [e-mail]             4

我的预期结果:

   PARTYID PHONE        FAX          MOBILE       EMAIL      
---------- ------------ ------------ ------------ ------------
       100 0354441010   0355551010   0428105789   [e-mail]

我的查询:

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

我遇到的错误:

Error starting at line 9 in command: 
FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email)) 
Error report: 
Unknown Command 

出现问题的原因是因为我的Oracle数据库版本(Oracle9i)不支持PIVOT功能.以下是用另一种方式进行操作的方法:

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:

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

推荐答案

在声明之后，您的语句中使用了分号:

You have a stray semi-colon in your statement, after:

    WHERE partyId = 100; 

删除该链接即可

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   [e-mail]

它被视为多个语句；第一个是不完整的，因为它缺少右括号(因此得到ORA-00907)，第二个从该括号开始并得到了您报告的错误，然后每行都得到相同的错误.您似乎只是在查看上一次报告的错误-从第一个错误开始，将其清除，然后在下一个仍然存在的情况下移至下一个错误通常会更有帮助.

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.

这篇关于Oracle SQL Developer:如何使用PIVOT函数将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！