0

From a query I retrieve the information I need which results into this:

code | category
-----------
1    |  A
1    |  A
2    |  B
3    |  B
3    |  B
4    |  B

From this data I would like to get the following result:

Combination 1&2 | Combination 3&4
---------------------------------
'Both A & B'    |  'Just B"

So what is happening is, if the first column takes all the rows for where code is 1 or 2. It then sees if in all these rows:

-Are all the rows equal to category A then result in the value "Just A"

-Are all the rows equal to category B then result in the value"Just B"

-Is there a mix of categories A and B's for codes 1 and 2 then result in the value"Both A & B".

This will again happen inside of the second column except now i will only look for codes 3&4. In practice I actually will be combining many codes into some of these column, so some columns will combine 2 codes, other can combine 10 codes, and so on. Essentially I am trying to see what category the combination of codes are. Are they all A's, are they all B's, or at they mixed?

user2924127
  • 6,034
  • 16
  • 78
  • 136
  • In your desired output, which combinations of codes do you need? Why only 1&2 and 3&4 and not, for example, 2&3? Remember rows in a table are not ordered, so that can't be the basis for your choice unless you designate a specific order. Also, what does "both" A&B mean when there are more than two categories? How do you treat category if it's null? –  Jun 01 '16 at 19:44
  • check this thread [Is there any function in oracle similar to group_concat in mysql? [duplicate]](http://stackoverflow.com/q/16771086/3486492) – clairerb6 Jun 01 '16 at 19:45

1 Answers1

1

You can use conditional aggregation:

select (case when min(category) <> max(category) then 'Both A and B'
             when min(category) = 'A' then 'A only'
             when min(category) = 'B' then 'B only'
         end)
from t
where code in (1, 2) and category in ('A', 'B');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786