CODEWITHSUNDEEP
sqloracle-database
HeartCore
HeartCore

Reputation: 49

Select two new columns based on values in one column

I have two tables, one with person data and one with external codes for these persons. These external codes can either be numerical (N for numeric code) or alphanumerical (S for short code). They are joined with a partner_id.

For every single person I'm supposed to return the columns "partner_id", "birth_date", "numeric_ext_code" (if exists, else null) [if the type of the ext_code is N] and "short_ext_code" (if exists, else null) [if the type of the ext_code is S].

Since I only have one column with each type of external code, I need to "split" this one into two - numeric_ext_code and short_ext_code. How do I do this? I can provide further information if needed.

Sample data:

sample data

Example where you can see that one person can have a numerical and an alphanumerical code at the same time.

sample data 2

Desired results: 

PARTNER_ID  | BIRTH_DATE | NUMERIC_EXT_CODE          | SHORT_EXT_CODE  
10000008611 | 15.01.63   | 0000000000000000090655955 | test11

(sorry for the bad formatting, I don't know any better way to do it)

Upvotes: 0

Views: 48

Answers (1)

Nick
Nick

Reputation: 147146

You can use conditional aggregation:

SELECT PARTNER_ID, BIRTH_DATE,
       MAX(CASE WHEN ext_code = 'N' THEN ext_code ELSE NULL END) AS NUMERIC_EXT_CODE,
       MAX(CASE WHEN ext_code = 'S' THEN ext_code ELSE NULL END) AS SHORT_EXT_CODE
FROM Person P 
JOIN External E ON P.PARTNER_ID = E.PARTNER_ID
GROUP BY PARTNER_ID, BIRTH_DATE

Upvotes: 1

Related Questions