CODEWITHSUNDEEP
sqloracle-databaseplsql
user2924127
user2924127

Reputation: 6242

transform and group data together

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?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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');

Upvotes: 1

Related Questions