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
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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 32

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
 
LVL 32

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 32

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 32

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 32

Expert Comment

by:awking00
ID: 35083570
Glad to help.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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