Oracle SQL - retrive data by GROUP BY and then derive the data based on the group by result

0
votes

I have a table with three columns, Col1, Col2 and Col3 where Col2 is primary key. Below is the SOURCE table data as

Col1    Col2    Col3
G1      S1      C
G1      S2      Y
G1      S3      U
G2      S4      C
G2      S5      Y
G3      S6      C
G3      S7      C
G4      S8      Y
G4      S9      Y
G5      S10     U
G5      S11     U
G6      S12     C
G6      S13     U
G7      S14     Y
G7      S15     U

Expected TARGET table will ahve only Col1 and Col3 and data should be

Col1    Col3
G1      U
G2      B
G3      C
G4      Y
G5      U
G6      U
G7      U

Logic used is:
1) For a given Col1 record, if all the col2 records have same value in Col3 then return one record for all the col2 records ( Examples G3, G4, G5)
2) For a given Col1 record, if all the col2 records having either 'C' or 'Y then return 'B' (Example G2)
3) For all the remaining combinations return 'U' for Col3

Any one on the group please advice me on how to write query to get the data in target table Thank you

1
sqloraclegroup-by
What have you tried thus far? This smells like homework to me but take that data and build it into a with clause so you can manipulate it. – mmmmmpie

1 Answers

1
votes

Use a CASE construct for this. For the 'C' and 'Y' thing you'd have to count conditionally (i.e. also with CASE).

select 
  col1, 
  case 
    when min(col3) = max(col3) then min(col3)
    when count(case when col3 not in ('C','Y') then 1 end) = 0 then 'B'
    else 'U'
  end as col3
from mytable
group by col1;