18

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?

Sergey Kalinichenko
  • 697,062
  • 78
  • 1,055
  • 1,465
MontyPython
  • 2,756
  • 11
  • 34
  • 58
  • 3
    Please consider searching before asking a question to save time and get a quicker answer for yourself. http://stackoverflow.com/questions/5301613/inverse-row-to-column/5301689#5301689 – SQLMason Nov 08 '13 at 14:02
  • Check out [this question's answers](https://stackoverflow.com/q/15491661/5841306) for dynamical options. – Barbaros Özhan Jan 07 '21 at 13:08

3 Answers3

20

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

Przemyslaw Kruglej
  • 7,893
  • 2
  • 25
  • 41
  • Isn't the first method posted by dasblinkenlight a better and elegant solution to this? Do shed light? – MontyPython Nov 08 '13 at 13:35
  • 3
    @MontyPython I've given you the solution that works both in Oracle 10g and 11g. Since you didn't write your Oracle version, I provided a solution which works for both. `PIVOT`, used by dasblinkenlight, was introduced in Oracle 11g and is unavailable in versions prior to 11g. – Przemyslaw Kruglej Nov 08 '13 at 13:57
  • I missed that. I work on 11g and I am new to Oracle. Thanks for both the solutions, though. Very helpful indeed. – MontyPython Nov 12 '13 at 07:34
15

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.

Sergey Kalinichenko
  • 697,062
  • 78
  • 1,055
  • 1,465
8
 select * FROM doc_tab
    PIVOT
    (
    Min(document_id)
    FOR document_type IN ('Voters ID','Pan card','Drivers licence')
    )

outputs as this

enter image description here

sql fiddle demo here

vhadalgi
  • 6,871
  • 6
  • 36
  • 67