Search code examples
sqlgroup-byoracle11gvarchar

Oracle query for getting desired output

ColA ColB ColC ColD
data1 rata1 T Test1
data1 rata3 F Test2
data1 rata2 T Test1
data2 rata1 T Test1
data2 rata3 T Test1
data3 rata4 T Test1

Have four columns of type Varchar. What i wish to determine is for each unique value of ColB(rata1, rata2 etc), find corresponding value of ColA and other two columns (ColC, ColD) where that specific value of ColA occurs 0 or 1 time. For example- For rata1, data1 and data2 occurs one time so the output would include the columns shown below in addition to other columns for other distinct values of ColB.

data1    rata1    T     Test1
data2    rata1    T     Test1

Would highly appreciate if anyone can provide any recommendations around the same.

Solution 
  • With MyQuery as (select ColB from MyTable group by ColB having count(ColA) <= 1) select
t.ColB, ColA, ColC, ColD from MyTable t inner join MyQuery q on t.ColB = q.ColB