Thursday, April 24, 2014
Home » SQL » How To Transpose Columns To Rows In Oracle
How To Transpose Columns To Rows In Oracle
How to transpose columns to rows in Oracle

How To Transpose Columns To Rows In Oracle

PL/SQL Developer

In this little tutorial, I will show you how you can transpose columns into rows in Oracle, using SQL. This post is a follow-up of a post where I showed how to transpose rows into columns.

Introduction

A while ago I wrote a tutorial on how to transpose rows to columns using Oracle SQL. You can find the tutorial here: Three Ways To Transpose Rows Into Columns in Oracle SQL

Many people would like to know how to do it the opposite way, transpose columns to rows, as well. So, here is a tutorial that shows a couple of ways you can do this.

Examples

In the tutorial we will pretend we have a survey table where the people taking the survey have filled out a form about what health concerns they have. Yes for a concern will be “1″ in the table, and No will be “0″ in the table.

We have the following records in the table:

For one of our company’s reports, we need to come up with a SQL query where the columns are represented as rows – meaning that we have transposed the columns to rows.

Example # 1: Using the UNION operator

In this sample, we will solve the task simply by using several UNION statements. Our end result should be a resultset with 30 records. The reason for this is that we have six (6) unique ids, and we have five (5) columns for each of the ids. And… 6 x 5 = 30 :-)

As you can see, we got our 30 records. Also, note that I used a WITH statement for my main query. This is because I want to make sure that my query does not pull data from the survey table five times, but only once.

Note: Based on your data, you might want to consider using UNION ALL rather than UNION, whatever gives you the correct result.

Example # 2: Using the UNPIVOT operator

If you have a database with Oracle database version 11g or later, you might consider using the UNPIVOT operator to solve your task.

Here is a sample:

As you can see, we have the same amount of records.

In the query above, the “concern_type” and the “concern_flag” fields can be named anything.

NOTE: If you choose to use the UNPIVOT operator – you need to be aware that if you are using an alias on your table, you might getting error messages. A query using UNPIVOT does not seem to like aliases very well.

Also, in the above query, you see that I use i.e. concern_eye AS ‘EYE’. This is a way that the aliasing works, so try to be careful.

Conclusion

Ok, so we have seen two different ways to transpose columns into rows in Oracle, using SQL. The first example might be a good way, but it requires a little bit more code.

The second example, using UNPIVOT, is a good way to go if your database version is 11g or newer. There are though a few things to be aware of using the UNPIVOT operator, especially when using aliases.

I hope this has been helpful for you. If you liked this post, please share this post with others :-)

~ TJ

In this little tutorial, I will show you how you can transpose columns into rows in Oracle, using SQL. This post is a follow-up of a post where I showed how to transpose rows into columns. Introduction A while ago I wrote a tutorial on how to transpose rows to columns using Oracle SQL. You ...

Review Overview

Rate this

Summary : If you enjoyed this post, rate it to let other people know what you think. Hey...don't forget to share as well :-)

User Rating: Be the first one !
0

About TJ Abrahamsen

TJ Abrahamsen is the founder and author of OracleTuts. He has worked with Oracle since 1997. His expertise is with Oracle SQL and PL/SQL. He loves teaching others, and like to share his thoughts, ideas, and experience on his blog.
  • Pingback: Three Ways To Transpose Rows Into Columns in Oracle SQL - OracleTuts

  • Jon

    Hi Terje

    Nice example!!, you rely can many tricks for using SQL and analytic features, keep up the good work :-)

    • http://oracletuts.net TJ Abrahamsen

      Hello Jon, my friend. Long time. It has been a few years with Oracle :-).
      Thank you for your comment.

  • Ab

    Hi TJ,

    I was wondering whether its possible( and if so how)to transpose columns to rows and rows to columns simultaneously. For example:
    I would like to get the result as

    CONCERN_TYPE 123 321 456 654 789 987

    CONCERN_EYE 1 0 1 1 0 0
    CONCERN_VISION 0 1 1 1 1 1
    CONCERN_BRAIN 1 0 0 0 0 0

    Great tutorial by the way!

    • http://oracletuts.net TJ Abrahamsen

      Hello Abhish –

      Thank you for your comment. Maybe you can look at my opposite tutorial?

      “Three Ways to Transpose Rows Into Columns”.

      ~ TJ

  • rajesh

    hi TJ:
    what if i want to display the perfect transpose. Meaning, i want the values of 321 after 123 in column wise and not in row wise. How to go about it?

    Thanks,
    Rj

    • rajesh

      Assume i have a 2×5 table like this.

      105 blue green black red
      106 red green white red

      i want to transpose this table and compare the values from two columns and display if they are the same in the third column.Like this

      105 106 notsame
      blue red notsame
      green green same
      black white notsame
      red red same

      I tried and achieved this using a lot of “unions”. But how do i do it with less complexity and code size?

  • Alex

    Hi TJ I really like your hair you look a lot like Jesus!!! Do you have cool sandals to?

    • http://oracletuts.net/ TJ Abrahamsen

      Hey Alex. Only during the summer. :-) He is my idol though, ;-)

      Thanks for checking in!

      ~ TJ

      • Alex

        Your welcome I really like your beard to your a handsome bear. Maybe we could get together sometime and talk about oracle.