Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Shrenik.

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

Answered by: Connor McDonald - Last updated: March 09, 2019 - 3:00 am UTC

Category: Database - Version: 11g

Viewed 100K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Datatype conversion laziness ... yet another reason

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 we 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/

and you rated our response

  (3 ratings)

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

Reviews

No Aggregate Funtion

August 31, 2018 - 3:08 pm UTC

Reviewer: BillC from Woodland Park, CO USA

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

Followup  

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

March 06, 2019 - 12:27 pm UTC

Reviewer: Praveen Darla from INDIA

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

Followup  

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

March 08, 2019 - 9:10 am UTC

Reviewer: Praveen Darla from INDIA

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

Followup  

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