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
288 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 108
TSQL - How to declare table name 26 43
Query group by data in SQL Server - cursor? 3 49
SQL Select in Access 2003 3 27
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 …
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…
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 …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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