Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Request for help to use PIVOT and UNPIVOT

Posted on 2012-09-10
Medium Priority
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
  • 3
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 38384401
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 74

Assisted Solution

sdstuber earned 2000 total points
ID: 38384408
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 74

Assisted Solution

sdstuber earned 2000 total points
ID: 38384417
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

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month10 days, 7 hours left to enroll

571 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