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
Chris JohnsenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.