Home » SQL & PL/SQL » SQL & PL/SQL » Pivot using SQL in Oracle (Oracle SQL Dev Version 17.3.1.279)
Pivot using SQL in Oracle [message #682303] Thu, 15 October 2020 10:03 Go to next message
calredd@yahoo.com
Messages: 1
Registered: October 2020
Junior Member
/foru/forum/fa/14404/0/

Hello Gurus,
I have data as follows in my table:

PID STATE START DT Month Exp Act
-------- ----- --------- ------ --- ---
PID51183 HI 27-Jul-20 202007 150 5
PID51183 HI 27-Jul-20 202008 150 65
PID51183 HI 27-Jul-20 202009 150 304
PID15175 CA 05-Dec-19 202007 300 174
PID15175 CA 05-Dec-19 202008 300 446
PID15175 CA 05-Dec-19 202009 300 329
PID99999 TX 21-Jul-20 202009 52 16
PID16588 TX 01-Jul-20 202007 500 94
PID16588 TX 01-Jul-20 202008 500 49
PID16588 TX 01-Jul-20 202009 500 60
PID15420 ME 20-Nov-19 202008 200 75
PID15420 ME 20-Nov-19 202009 200 189

I need to pivot the data on Month (4th column) and display the Act data (last column) shown above. I resultant data should looks like:

PID STATE START DT Exp 202007 202008 202009
-------- ----- --------- --- ------ ------ ------
PID51183 HI 27-Jul-20 150 5 65 304
PID15175 CA 05-Dec-19 300 174 446 329
PID99999 TX 21-Jul-20 52 16
PID16588 TX 01-Jul-20 500 94 49 60
PID15420 ME 20-Nov-19 200 75 189


I have also shown the data in the picture. Hope the problem is clear and look forward to suggestions on how to implement this using SQL.
Thanks,
C
  • Attachment: Data.JPG
    (Size: 110.24KB, Downloaded 34 times)
Re: Pivot using SQL in Oracle [message #682305 is a reply to message #682303] Thu, 15 October 2020 10:23 Go to previous message
Michel Cadot
Messages: 67538
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.

Previous Topic: ORA-06502
Next Topic: How to generate a spool file without an extension name (.lst,.txt etc...)
Goto Forum:
  


Current Time: Mon Nov 23 16:56:38 CST 2020