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