Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Shrenik.

Asked: June 25, 2016 - 8:58 am UTC

Last updated: March 09, 2019 - 3:00 am UTC

Version: 11g

Viewed 100K+ times! This question is

You Asked

I have a table like:

Client Process Date Status

A B 21-june Y

C D 22-june N

A B 22-june N

C D 21-june Y

..

..

n rows



I want to display a report in the following format:

Client Process 21-June 22-june .. .... n colums

A B Y N

C D Y N



Please help with the query

and Connor said...

If the row values (and hence columns that will result) is fixed and known in advance, you can use a simple PIVOT clause

See here for a simple example

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

If you need to dynamically generate the column list, then check out this facility built by the guys at AMIS

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

No Aggregate Funtion

BillC, August 31, 2018 - 3:08 pm UTC

Sorry Tom but from the link supplied I couldn't figure how to transpose with out using an aggregate function.

The question posted seems identical to my need which has no aggregates.

What I have:
select label, value from mytbl
labelA W
labelB X
labelC Z

What I want:
labelA, labelB, labelC
W X Z

Did I miss how to do it ?
Chris Saxon
August 31, 2018 - 3:40 pm UTC

You can find a worked example on my blog:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#transpose

You need to use an aggregate that will do a no-op on your values. For strings you could use min or max.

How to Covert rows into columns using collections at a time

Praveen Darla, March 06, 2019 - 12:27 pm UTC

table1
-------
column1 || column 2
------- ---------
a b

expected result
variable
--------
a
b

If more columns are there then print the row values into table using collections for better performance.

Please help me out.


Chris Saxon
March 06, 2019 - 1:06 pm UTC

That's just an unpivot:

select * from t
unpivot ( 
  val for col in ( col1, col2 )
);


If more columns are there then print the row values into table using collections for better performance.

I'm not sure what you're getting at here. Why do you want to use collections and why do you think they'll improve performance?

Converting rows into columns

Praveen Darla, March 08, 2019 - 9:10 am UTC

Thanks for Unpivot , is good but performance wise it might not be useful .

Is that possible to achieve through collections.

If possible please give some example.
Connor McDonald
March 09, 2019 - 3:00 am UTC

Once "might" becomes "and here is my test case proving the issue" then we'll be able to take a look at it