Solved

SQL --- date fields as two seperate columns from the same table based on criteria from a seperate field

Posted on 2011-03-08
13
284 Views
Last Modified: 2012-05-11
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
Comment
Question by:aehrenwo
13 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 35070989
Its not clear that what exactly do you need, but it seems you need the pivoted result. For this read the PIVOT command.
0
 

Author Comment

by:aehrenwo
ID: 35071059
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
 
LVL 40

Expert Comment

by:Sharath
ID: 35071234
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
 

Author Comment

by:aehrenwo
ID: 35071340
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
 
LVL 31

Expert Comment

by:awking00
ID: 35073263
What dbms and version are you using?
0
 

Author Comment

by:aehrenwo
ID: 35073307
I am running scripts in Rapid SQL version 7.6

The database itself is Oragle 10g..

What else do you need to know?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:awking00
ID: 35073653
I was just hoping the dbms was Oracle 11g, which introduced the pivot query.
Sorry :-(
0
 

Author Comment

by:aehrenwo
ID: 35073708
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
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
ID: 35073842
See attached.
queries.txt
0
 

Author Comment

by:aehrenwo
ID: 35083042
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
 
LVL 31

Expert Comment

by:awking00
ID: 35083158
See attached.
comment.txt
0
 

Author Closing Comment

by:aehrenwo
ID: 35083449
it did almost exactly what I needed. Very knowledgeable and friendly.
0
 
LVL 31

Expert Comment

by:awking00
ID: 35083570
Glad to help.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now