如何在Oracle中将行转换为列? [英] How to convert Rows to Columns in Oracle?

问题描述

我有一个这种形式的表(这只是局部视图，该表包含100多个列).

 LOAN NUMBER   DOCUMENT_TYPE                DOCUMENT_ID
 992452533663  Voters ID                    XPD0355636
 992452533663  Pan card                     CHXPS5522D
 992452533663  Drivers licence              DL-0420110141769

对于一个单一的贷款号码，我有三种文件作为证明.我希望将这些详细信息转换为列并采用以下形状:

LOAN NUMBER     VOTERS_ID    PAN_CARD     DRIVERS LICENCE
992452533663    XPD0355636   CHXPS5522D   DL-0420110141769

如何处理?

解决方案

如果使用的是Oracle 10g，则可以使用DECODE函数将行旋转为列:

CREATE TABLE doc_tab (
  loan_number VARCHAR2(20),
  document_type VARCHAR2(20),
  document_id VARCHAR2(20)
);

INSERT INTO doc_tab VALUES('992452533663', 'Voters ID', 'XPD0355636');
INSERT INTO doc_tab VALUES('992452533663', 'Pan card', 'CHXPS5522D');
INSERT INTO doc_tab VALUES('992452533663', 'Drivers licence', 'DL-0420110141769');

COMMIT;

SELECT
    loan_number,
    MAX(DECODE(document_type, 'Voters ID', document_id)) AS voters_id,
    MAX(DECODE(document_type, 'Pan card', document_id)) AS pan_card,
    MAX(DECODE(document_type, 'Drivers licence', document_id)) AS drivers_licence
  FROM
    doc_tab
GROUP BY loan_number
ORDER BY loan_number;

输出:

LOAN_NUMBER   VOTERS_ID            PAN_CARD             DRIVERS_LICENCE    
------------- -------------------- -------------------- --------------------
992452533663  XPD0355636           CHXPS5522D           DL-0420110141769     

您可以使用11g中引入的Oracle PIVOT子句实现相同的目的:

SELECT *
  FROM doc_tab
PIVOT (
  MAX(document_id) FOR document_type IN ('Voters ID','Pan card','Drivers licence')
);

带有两种解决方案的SQLFiddle示例: SQLFiddle示例

在此处详细了解如何进行透视:透视蒂姆·霍尔(Tim Hall)的甲骨文(Oracle)

I have a table in this form (this is just the partial view, the table contains more than 100 columns).

 LOAN NUMBER   DOCUMENT_TYPE                DOCUMENT_ID
 992452533663  Voters ID                    XPD0355636
 992452533663  Pan card                     CHXPS5522D
 992452533663  Drivers licence              DL-0420110141769

For a single loan number, I have three kinds of documents as proof. I want these details to be converted into columns and take the following shape:

LOAN NUMBER     VOTERS_ID    PAN_CARD     DRIVERS LICENCE
992452533663    XPD0355636   CHXPS5522D   DL-0420110141769

How to go about this?

解决方案

If you are using Oracle 10g, you can use the DECODE function to pivot the rows into columns:

CREATE TABLE doc_tab (
  loan_number VARCHAR2(20),
  document_type VARCHAR2(20),
  document_id VARCHAR2(20)
);

INSERT INTO doc_tab VALUES('992452533663', 'Voters ID', 'XPD0355636');
INSERT INTO doc_tab VALUES('992452533663', 'Pan card', 'CHXPS5522D');
INSERT INTO doc_tab VALUES('992452533663', 'Drivers licence', 'DL-0420110141769');

COMMIT;

SELECT
    loan_number,
    MAX(DECODE(document_type, 'Voters ID', document_id)) AS voters_id,
    MAX(DECODE(document_type, 'Pan card', document_id)) AS pan_card,
    MAX(DECODE(document_type, 'Drivers licence', document_id)) AS drivers_licence
  FROM
    doc_tab
GROUP BY loan_number
ORDER BY loan_number;

Output:

LOAN_NUMBER   VOTERS_ID            PAN_CARD             DRIVERS_LICENCE    
------------- -------------------- -------------------- --------------------
992452533663  XPD0355636           CHXPS5522D           DL-0420110141769     

You can achieve the same using Oracle PIVOT clause, introduced in 11g:

SELECT *
  FROM doc_tab
PIVOT (
  MAX(document_id) FOR document_type IN ('Voters ID','Pan card','Drivers licence')
);

SQLFiddle example with both solutions: SQLFiddle example

Read more about pivoting here: Pivot In Oracle by Tim Hall

这篇关于如何在Oracle中将行转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！