0

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

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
| improve this answer | |
  • Hi, the result that I'm getting it's querying from multiple tables, and the one column only should get from my tableName here, something like select table1.x, table2.y, table3.z, tableName.subID where tableName.mainID=table1.mainID. Does this can give the same result? How can I change this part "tableName.subID " with the one in your body of answer? – αғsнιη Dec 12 '16 at 13:24
  • 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 Dec 12 '16 at 13:27
  • See updated answer – David דודו Markovitz Dec 12 '16 at 13:28
  • Please send me your code (see my profile for my mail address) – David דודו Markovitz Dec 19 '16 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.