How to query and group based on matched multiple conditions? 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
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