Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an Oracle table with the following rows:

Machine
OEEDate
Shift1
Shift2
Shift3

The data is has looks similar to this:
500, 8/1/2017, 0, 0, 1
500, 8/2/2017, 1, 1, 1

I have a whole years worth of data in the table. I also have about 50 different machines for each date. I would like to make a query where I can display a whole weeks worth of data (7 days Monday-Sunday) for a particular machine but have the data appear in columns versus multiple rows. Here is what I am looking for. To simplify things, I am only showing 2 days worth of data. Since the same column name is being repeated, I will need to rename each column for each day.

machine, oeedate-M, Shift1-M, Shift2-M, Shift 3-M, oeedate-T, Shift1-T, Shift3-T

500, 8/1/2017, 0, 0, 1, 8/2/17, 1, 1, 1

What I have tried:

I think this would be an application for using the case statement but I cannot figure out the correct syntax. I have only used the Case statement once before so I am pretty new using it. This is what I have tried but it is not working out:

select machine, oeedate, shift1, shift2, shift3,
(case when oeedate = TO_DATE('08/01/2017 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') then oeedate end) as shift1A,
(case when oeedate = TO_DATE('08/02/2017 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') then oeedate end) as shift1B
from plannedproductiontimes      
where machine = '500'


In case it matters, we are still running Oracle 9 at the moment.
Posted
Updated 26-Aug-17 1:32am
v2

Since you say TRANSPOSE (PIVOT) does not work in 9i you can try this in lieu of CASE statement. Though there are subqueries it is easier than using CASE in this particular context. (Just check the part to_char(<date>, 'day') - that it returns Day of Week - I got that on Google)

SQL
select mon.*, tue.*, wed.* .. sun.*
from
(
select machine, to_char(oeedate, 'mm/dd/yyyy') monday, shift1_mon, shift2_mon, shift3_mon
from plannedproductiontimes
where upper(substr(to_char(oeedate, 'day'), 1, 3)) = 'MON'
and machine = '500'
) mon,
(
select to_char(oeedate, 'mm/dd/yyyy') tuesday, shift1_tue, shift2_tue, shift3_tue
from plannedproductiontimes
where upper(substr(to_char(oeedate, 'day'), 1, 3)) = 'TUE'
and machine = '500'
) tue,
...
...
(
) sun
where to_date(tuesday, 'mm/dd/yyyy') = to_date(monday, 'mm/dd/yyyy') + 1
and to_date(wednesday, 'mm/dd/yyyy') = to_date(monday, 'mm/dd/yyyy') + 2
...
...
and to_date(sunday, 'mm/dd/yyyy') = to_date(monday, 'mm/dd/yyyy') + 6
order by machine, to_date(monday, 'mm/dd/yyyy')
; 
 
Share this answer
 
Comments
theskiguy 28-Aug-17 8:20am    
Thank you very much. This is very close to what I need. I did make a few minor changes. I only wanted to show only week's work of data so I added another item to the final WHERE clause. Also, I had to change the syntax dealing with the column acronyms for the shifts columns.

My only problem I have now is trying to figure out what to do if I want to display this same week's worth of data for more then 1 machine at a time sorted by machine. If I add the additional machine into the WHERE clause, it creates duplicate results. I guess I could UNION the results for each machine together but with over 50 machines, I am trying to figure out if there is a better way.
RAMASWAMY EKAMBARAM 29-Aug-17 1:16am    
"for more than 1 machine" - I did not overlook the requirement but you can complete it yourself. Instead of querying for a particular machine ("where machine = '500'") you will have to select the machine for each of the days (in my example I have done it only for 'monday') and alias as 'mch_mon', 'mch_tue' .. and in the condition for joining the sub queries you will need to match the machines (mch_mon = mch_tue) in addition to matching the dates.
The only drudgery is that you will need to spell out the individual columns (aliases) for each day rather than simply writing mon.*, tue.* ...!
Hello,

I think the PLSQL PIVOT CLAUSE does precisely what you're trying to do here.

I suggest you take a look and try it. You may have to combine the results from your pivoted tables but it does the trick.

Post question update:

I think you're on the right track with your query, you just need to form a better query.
You can take some ideas from here (manual pivoting) or here.

You may have to spend some time figuring out how to aggregate your results the way you want, nonetheless, this is the way to go.

Thanks and good luck.
 
Share this answer
 
v4
Comments
theskiguy 25-Aug-17 9:59am    
Sorry but I forgot to mention we are still running Oracle 9. It appears this command requires Oracle 11. I updated my question to add the Oracle 9 requirement.
jgakenhe 25-Aug-17 10:48am    
XMLAGG will work. You might want to look here for an example when compared to PIVOT: http://www.dba-oracle.com/t_converting_rows_columns.htm
theskiguy 25-Aug-17 15:15pm    
From my initial tests, it looks like XMLAGG works but it puts all the values into one column separated by commas. I really need a separate column for each value so I can display in my data grid. Am I missing something?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900