20

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
  • 714,442
  • 84
  • 1,110
  • 1,523
MontyPython
  • 2,906
  • 11
  • 37
  • 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
  • Just an FYI though question may be duplicate but different answers help in this case. Solution in both the links does not work for me. But the solution here works like a charm. – ragethewolf May 17 '23 at 18:13

3 Answers3

23

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
  • 8,003
  • 2
  • 26
  • 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
  • 1
    Loved this answer as it worked in a restricted access DB too. Where pivot does not work basically where I work we can access oracle only through SAP BO. – ragethewolf May 17 '23 at 18:12
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
  • 714,442
  • 84
  • 1,110
  • 1,523
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
  • 7,027
  • 6
  • 38
  • 67