Request for help to use PIVOT and UNPIVOT

Posted on 2012-09-10
Last Modified: 2012-09-10
I've never used pivot before and I'm trying to learn how to use it. i'm not finding any helpful beginner examples that are easy for me to understand on the web. Can anyone show me any cool tips and tricks and just how I can use PIVOT and UNPIVOT?

I attached a picture of a sample table in my schema for you to give me some cool example SQL code I can mess with for using pivot and unpivot. Thanks

I was trying this for example but it's not right:

Open in new window

Question by:Mark_Co
    LVL 73

    Accepted Solution

    please, no screen captures  it's always easier to work with text
    also, input without expected output doesn't tell us what you're trying to do

    however, for pivot you can use any data,
    so in my examples I'll use dba_objects (use all_objects instead if you don't have access)

    the first query "original" shows the raw input data I'm trying to pivot

    the next two show two different ways of pivoting those rows into columns
    LVL 73

    Assisted Solution

    for unpivot I'll use dba_users

    same idea as before.  "original" shows the raw data I want to unpivot.

    the next two queries show how to unpivot those columns into rows
    LVL 73

    Assisted Solution

    note, when doing an unpivot if you try to unpivot multiple data types into a single column you'll get an error.  This makes sense since a single column can't be both a number and a date for example.  

    The work around for that is to convert the data to strings first using TO_CHAR or CAST then unpivot those results

    Author Closing Comment

    Thank you. That did help me understand it a lot better.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    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…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

    755 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

    21 Experts available now in Live!

    Get 1:1 Help Now