[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
295 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

656 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