0

I have 2 different data sources in an Oracle DB. This is just a SQL connection to the Oralce DB (not PL SQL).

1st source is an application table with an app ID and a large list of attributes 2nd source is a table of contacts. each contact has an app ID (logical_name), a type, a contact an email and some other properties.

I want to put them in an application table in a target location that

I searched here and based on what I learned ended up trying to join my app table to this sub-query:

select 
max(logical_name) logical_name,
MAX(CASE WHEN type='APPLICATION SUPPORT OWNER' THEN contact END) Application_Support_Owner,
MAX(CASE WHEN type='APPLICATION SUPPORT OWNER' THEN email END) App_Support_Owner_EMAIL,
MAX(CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN contact END) Application_Support_Primary,
MAX(CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN email END) App_Support_Primary_EMAIL,
MAX(CASE WHEN type='BUSINESS CONTACT - PRIMARY' THEN contact END) Business_Contact_Primary,
MAX(CASE WHEN type='BUSINESS CONTACT - PRIMARY' THEN email END) Business_Contact_Primary_EMAIL

from contact_table
group by logical_name

What I discovered when I looked at the output is that in some cases there is more than one contact of a specific type (despite names like "primary" and "owner"). Because contacts are "Last, First" format and emails are First.Last@company.com, the Max function is mixing and matching contacts and emails.

I'm thinking the best answer for multiple values is just to get the contact and email from one record and throw the other one away. It is simpler and we only really want one contact for each role (i.e. of each type).

I'm a bit of a SQL noob. Is there a function I could use other than Max() or a different syntax that would yield two fields from the same record? I only want one row of data for each app so my join doesn't have multiple records for each app. Thanks in advance for your assistance.

Tim B
  • 876
  • 1
  • 7
  • 12
  • Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) Or any of these: https://stackoverflow.com/search?q=%5Bsql%5D+%5Boracle%5D+transpose+rows+to+columns – William Robertson Nov 03 '21 at 21:14
  • 2
    @WilliamRobertson The description of the problem is convoluted and a bit hard to follow (it could be improved if the OP added sample data and expected output) but the OP has successfully pivoted the table but has multiple possible rows in each pivot group and needs to make sure during conditional aggregation that rows are correlated in the pivot across pairs of columns; none of your links are relevant to that problem. – MT0 Nov 04 '21 at 14:28
  • Thanks @MT0. Like I said, I'm new at this, so I don't know the correct terms to ask succinctly. The solution below is exactly what I was looking for. – Tim B Nov 04 '21 at 16:06

1 Answers1

1

Use KEEP with the aggregation function to correlate the aggregations:

select max(logical_name) logical_name,
       MAX(CASE WHEN type='APPLICATION SUPPORT OWNER' THEN contact END)
         AS Application_Support_Owner,
       MAX(CASE WHEN type='APPLICATION SUPPORT OWNER' THEN email END)
         KEEP(
           DENSE_RANK LAST
           ORDER BY CASE WHEN type='APPLICATION SUPPORT OWNER' THEN contact END NULLS FIRST
         ) AS App_Support_Owner_EMAIL,
       MAX(CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN contact END)
         AS Application_Support_Primary,
       MAX(CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN email END)
         KEEP(
           DENSE_RANK LAST
           ORDER BY CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN contact END NULLS FIRST
         ) AS App_Support_Primary_EMAIL,
       MAX(CASE WHEN type='BUSINESS CONTACT - PRIMARY' THEN contact END)
         AS Business_Contact_Primary,
       MAX(CASE WHEN type='BUSINESS CONTACT - PRIMARY' THEN email END)
         KEEP(
           DENSE_RANK LAST
           ORDER BY CASE WHEN type='APPLICATION SUPPORT - PRIMARY' THEN contact END NULLS FIRST
         ) AS Business_Contact_Primary_EMAIL
from     contact_table
group by logical_name;
MT0
  • 143,790
  • 11
  • 59
  • 117