22

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?

Improve this question
3
  • 3
    Please consider searching before asking a question to save time and get a quicker answer for yourself. stackoverflow.com/questions/5301613/inverse-row-to-column/… Commented Nov 8, 2013 at 14:02
  • Check out this question's answers for dynamical options. Commented Jan 7, 2021 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. Commented May 17, 2023 at 18:13

3 Answers 3

Reset to default
26

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Isn't the first method posted by dasblinkenlight a better and elegant solution to this? Do shed light?
@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.
I missed that. I work on 11g and I am new to Oracle. Thanks for both the solutions, though. Very helpful indeed.
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.
16

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.

Improve this answer

2 Comments

hi. Is there any way to take the in-clause values dynamically?
@PKS As long as the number of in cases is fixed, yes; otherwise, no.
9 
 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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.