How to convert Rows to Columns in Oracle? [duplicate]

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_tabGROUP BY loan_numberORDER 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_tabPIVOT (  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

You can do it with a pivot query, like this:

select * from (   select LOAN_NUMBER, DOCUMENT_TYPE, DOCUMENT_ID   from my_table t)pivot (   MIN(DOCUMENT_ID)   for DOCUMENT_TYPE in ('Voters ID','Pan card','Drivers licence'))

Here is a demo on sqlfiddle.com.

 select * FROM doc_tab    PIVOT    (    Min(document_id)    FOR document_type IN ('Voters ID','Pan card','Drivers licence')    ) 

outputs as this

sql fiddle demo here