• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 34
  • Last Modified:

Combine 6 column datapoint to 3 columns with joining data

Working with oracle SQL that has two different tables and need to create a view to combine the data.  having a bit of trouble combining 6 data points to just 3 and have all of the data show in the 3.  see the xlsx that is attached, first tab is the data and how it is now and how I would like it and second is the SQL.
Thank you in advance for any help
list_update.xlsx
0
Chris Johnsen
Asked:
Chris Johnsen
1 Solution
 
johnsoneSenior Oracle DBACommented:
The easiest way I can think of to do it is to make it into 2 queries with a UNION.  This is just a shell, you need to adjust the FROM and WHERE clause, but this should give you the idea:
SELECT phase_settings.display_name     PHASE_SETTINGS_DISPLAY_NAME, 
       user_settings_1.display_name    USER_SETTINGS_1_DISPLAY_NAME, 
       document.etq$due_date           DOCUMENT_ETQ$DUE_DATE, 
       document.issue_name             DOCUMENT_ISSUE_NAME, 
       issue_type_1.description        ISSUE_TYPE_1_DESCRIPTION, 
       document.issue_opr              DOCUMENT_ISSUE_OPR, 
       sec_safety_risk_mgmt.etq$number SAFETY_RISK_MGMT_ETQ$NUMBER 
FROM   hcs.document DOCUMENT 
...
UNION ALL 
SELECT sec_phase_settings_1.display_name PHAS_SETTING_ETQ$CURRENT_PHASE, 
       sec_user_settings_1.display_name  USER_SETTINGS_ETQ$ASSIGNED, 
       sec_safety_risk_mgmt.etq$due_date SAFETY_RISK_MGMT_ETQ$DUE_DATE, 
       document.issue_name               DOCUMENT_ISSUE_NAME, 
       issue_type_1.description          ISSUE_TYPE_1_DESCRIPTION, 
       document.issue_opr                DOCUMENT_ISSUE_OPR, 
       sec_safety_risk_mgmt.etq$number   SAFETY_RISK_MGMT_ETQ$NUMBER 
FROM   hcs.document DOCUMENT 
...

Open in new window

1
 
awking00Commented:
I believe johnsone definitely has you on the right track. Rather than showing the list that was produced, it might be more helpful to show the tables involved with the data that produced that list, since the join clauses to produce the top select query and bottom select query in the union query will likely be quite different and with less complexity. As a note, the resulting column names from a union [all] query will always be the column names or their aliases of the initial select. So, for example, the following query:
select document.etq$due_date as due_dates
from document ...
union all select sec_safety_risk_mgmt.etq$due_date
from sec_safety_risk_mgmt ...

Would produce results with a heading of just due_dates.
1
 
Chris JohnsenAuthor Commented:
This aspect is what was missing in the items I was trying to do.  Thank you for your 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

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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