oracle: transpose rows to columns

Posted on 2012-08-22
Last Modified: 2012-08-22

I have a table that looks like this
123              col1                ABC
123              col2                9999
123              col3                Stop!
456              col1                 QQQ
456              col2                 87493
456              col3                 All Done

I need to transpose this to look like
EMPLID       col1           col2        col3
123             ABC           9999       Stop!
456             QQQ          87493     All Done

In the most efficient way possible. All fields are of type varchar
Question by:jorbagw
    1 Comment
    LVL 25

    Accepted Solution

    SELECT emplid
         , MAX(CASE WHEN col_name = 'col1' THEN col_val ELSE NULL END) as col1
         , MAX(CASE WHEN col_name = 'col2' THEN col_val ELSE NULL END) as col2
         , MAX(CASE WHEN col_name = 'col3' THEN col_val ELSE NULL END) as col3
    FROM your_table
    GROUP BY emplid

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now