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?
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
Source: Stackoverflow.com