How to query and group based on matched multiple conditions?

Question

I have a tableName with records as sample as following, the mainID is unique at all for same "name", but subID has different values (one or more) for same mainID(s),

name mainID subID
___________________
A 1234 12
A 1234 19
B 2341 12
C 3412 19
A 1234 3
B 2341 3
D 4123 12
C 3412 12
E 5123 3
F 1235 19

I want to have a result as following and mapping them with a "String" as sample like below.

name mainD subID
__________________
A 1234 X
B 2341 YY
C 3412 Y
D 4123 Z
E 5123 V
F 1235 Q

the mapping I'm defining are like below,

if (subID for same mainID matched _all_ 12, 19 and 3) then map it to "X"
if (subID for same mainID matched _only_ 12, and 3) then map it to "YY"
if (subID for same mainID matched _only_ 12 and 19 ) then map it to "Y"
if (subID for same mainID matched _only_ 19 and 3 ) then map it to "YYY"
if (subID for same mainID matched _only_ 12) then map it to "Z"
if (subID for same mainID matched _only_ 3) then map it to "V"
if (subID for same mainID matched _only_ 19) then map it to "Q"

What I tried was/is make them inside a CASE WHEN ... THEN ... END, but this matched one name like "A" print in multiple rows, with mapped to "Z", "V", "Q".

Can anyone please help to achieve the expected query result, thanks in advance

Answer 1

select name
 ,mainID
 ,decode
 (
 "3" || "12" || "19"
 ,'ABC' ,'X'
 ,'AB' ,'YY'
 ,'BC' ,'Y'
 ,'AC' ,'YYY'
 ,'B' ,'Z'
 ,'A' ,'V'
 ,'C' ,'Q'
 ) as subID
from mytable pivot (max(decode(subID,3,'A',12,'B',19,'C')) for subID in (3,12,19))
order by name
;

---

select name
 ,mainID
 ,decode
 (
 "3" || "12" || "19"
 ,'ABC' ,'X'
 ,'AB' ,'YY'
 ,'BC' ,'Y'
 ,'AC' ,'YYY'
 ,'B' ,'Z'
 ,'A' ,'V'
 ,'C' ,'Q'
 ) as subID
from (select name,mainID,subID from ...) t 
 pivot (max(decode(subID,3,'A',12,'B',19,'C')) for subID in (3,12,19))
order by name

• David דודו Markovitz: Wrap your query with brackets and alias it. Make sure it has only the columns name, mainID and subID. Put it instead of mytable in my query.
• David דודו Markovitz: See updated answer
• David דודו Markovitz: Please send me your code (see my profile for my mail address)