How to convert Rows to Columns in Oracle

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to pivot