SQL --- date fields as two seperate columns from the same table based on criteria from a seperate field
I have a table that holds three columns that are critical to my SQL eporting.
The first field is RPTKEY(unique identifier of the records), next is the EVTDATE. The last is the ACTION.
Is there a way to do a join or a union (not sure what is appropriate) so I can run a report that would show the ACTION across the top and the associated EVTDATE for that ACTION below with the RPTKEY as the value along has the row header.
The linkage would be the RPTKEY for both the other values.
Hope this makes sense.
The first field is RPTKEY(unique identifier of the records), next is the EVTDATE. The last is the ACTION.
Is there a way to do a join or a union (not sure what is appropriate) so I can run a report that would show the ACTION across the top and the associated EVTDATE for that ACTION below with the RPTKEY as the value along has the row header.
The linkage would be the RPTKEY for both the other values.
Hope this makes sense.
devlab2012
Its not clear that what exactly do you need, but it seems you need the pivoted result. For this read the PIVOT command.
ASKER
I would like to ideally have SQL results that have the RPTKEY along the left with the different ACTION across the top and the EVTDATE associated with those ACTION(s). All values are all held within the same table called EVT.
If this is not possible I would be okay with only showing two or three of the actions using a filter.. some ACTION values include APPROVE, SUBMIT, EXTRACT
Thank you in advance for any help with acutal code. I am not familiar at all with the PIVOT comomand.
If this is not possible I would be okay with only showing two or three of the actions using a filter.. some ACTION values include APPROVE, SUBMIT, EXTRACT
Thank you in advance for any help with acutal code. I am not familiar at all with the PIVOT comomand.
What do you mean by TOP action? Which one is top among APPROVE, SUBMIT and EXTRACT? How do you define TOP action? What are the possible combinations for action? What is your SQL version?
ASKER
let me provide an example of what the table looks like and what I would like to see if possible. See first table below ...
RPTKEY ACTION EVTDATE
1 EXTRACT 1/1/2010
2 EXTRACT 1/7/2010
3 EXTRACT 1/10/2010
1 SUBMIT 1/3/2010
2 SUBMIT 1/5/2010
3 SUBMIT 1/9/2010
1 APPROVE 1/18/2010
2 APPROVE 1/20/2010
3 APPROVE 1/23/2010
I want to code something that will display that table this way instead (putting all of the ACTION's across the top) there are over 20 different kinds (not just the 3 I mentioned). See below
RPTKEY EXTRACT SUBMIT APPROVE
1 1/1/2010 1/3/2010 1/18/2010
2 1/7/2010 1/5/2010 1/20/2010
3 1/10/2010 1/9/2010 1/23/2010
Hope this makes more sense. I using Rapis SQL 7 and its an Oracle 10g databse.
Adam
RPTKEY ACTION EVTDATE
1 EXTRACT 1/1/2010
2 EXTRACT 1/7/2010
3 EXTRACT 1/10/2010
1 SUBMIT 1/3/2010
2 SUBMIT 1/5/2010
3 SUBMIT 1/9/2010
1 APPROVE 1/18/2010
2 APPROVE 1/20/2010
3 APPROVE 1/23/2010
I want to code something that will display that table this way instead (putting all of the ACTION's across the top) there are over 20 different kinds (not just the 3 I mentioned). See below
RPTKEY EXTRACT SUBMIT APPROVE
1 1/1/2010 1/3/2010 1/18/2010
2 1/7/2010 1/5/2010 1/20/2010
3 1/10/2010 1/9/2010 1/23/2010
Hope this makes more sense. I using Rapis SQL 7 and its an Oracle 10g databse.
Adam
What dbms and version are you using?
ASKER
I am running scripts in Rapid SQL version 7.6
The database itself is Oragle 10g..
What else do you need to know?
The database itself is Oragle 10g..
What else do you need to know?
I was just hoping the dbms was Oracle 11g, which introduced the pivot query.
Sorry :-(
Sorry :-(
ASKER
we are upgrading in a few months to 11g. I can wait until then.
Are there any other options? Even if I wanted to focus on a few of the ACTION types?
Are there any other options? Even if I wanted to focus on a few of the ACTION types?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry... forgot to mention that I only have read access on the database. I assume that would prevent me from using the "Create table" function right in the txt file you sent?
See attached.
comment.txt
comment.txt
ASKER
it did almost exactly what I needed. Very knowledgeable and friendly.
Glad to help.