• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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.
0
aehrenwo
Asked:
aehrenwo
1 Solution
 
devlab2012Commented:
Its not clear that what exactly do you need, but it seems you need the pivoted result. For this read the PIVOT command.
0
 
aehrenwoTechnology AnalystAuthor Commented:
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.
0
 
SharathData EngineerCommented:
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?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aehrenwoTechnology AnalystAuthor Commented:
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






0
 
awking00Commented:
What dbms and version are you using?
0
 
aehrenwoTechnology AnalystAuthor Commented:
I am running scripts in Rapid SQL version 7.6

The database itself is Oragle 10g..

What else do you need to know?
0
 
awking00Commented:
I was just hoping the dbms was Oracle 11g, which introduced the pivot query.
Sorry :-(
0
 
aehrenwoTechnology AnalystAuthor Commented:
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?
0
 
awking00Commented:
See attached.
queries.txt
0
 
aehrenwoTechnology AnalystAuthor Commented:
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?

0
 
awking00Commented:
See attached.
comment.txt
0
 
aehrenwoTechnology AnalystAuthor Commented:
it did almost exactly what I needed. Very knowledgeable and friendly.
0
 
awking00Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now