Oracle Grouping by data value in a column

Question

Helo, I need to group based on data values.When there is 100 in COl1 a new group starts and I need to fill the values from Col2. There is ordering by another column col3( looks like some sequence..) Below output shown.I am on Oracle 12C release 1

COl1    Col2       Col3
-----   -----     -----
100      ABC inc.   1
201      Z1         2
201      Z2         3
301      X1         4
100     PQR inc.    5
201      C1         6
301      D1         7
201      S1         8
100     XYZ inc.    9
201      D1         10

Output :

Col1      COl2
----      -----
ABC inc.  Z1
ABC inc.  Z2
ABC inc.  X1
PQR inc.  C1
PQR inc.  D1
PQR inc.  S1
XYZ inc.  D1

Thanks

i don't see any details .. you have not explained the logic for grouping .. — ScaisEdge, Jun 22 '17 at 15:00
Yes .. the question is clear but i can't help you in sql this is not possible .. you could use a procedural languange as a server side or a trigger .. — ScaisEdge, Jun 22 '17 at 15:06
Is there another column you are not showing us? What would be the `ORDER BY` clause for the record order you are showing us? — Thorsten Kettner, Jun 22 '17 at 15:25
Yes you guys are right, I checked with other team,there is order by another column which is just looks like a sequence to me. I will add that to the question — Rajiv A, Jun 22 '17 at 15:33

score 3 · Accepted Answer · answered Jun 22 '17 at 16:03

3

Okay, now that there is col3 for the order: Count the occurrences of col1 = 100 up to each row. This count can be used as a group key.

with data as
(
  select 
    col1, col2, col3, count(case when col1 = 100 then 1 end) over (order by col3) as grp
  from mytable
)
select master.col2 as col1, slave.col2 as col2
from (select * from data where col1 = 100) master 
join (select * from data where col1 <> 100) slave using(grp)
order by grp, slave.col3;

answered Jun 22 '17 at 16:03

Thorsten Kettner

89,309
7
49
73

That's some sweet SQL magic right there :-) Tested in SQL Fiddle myself and it works. Can scrap my approach now... – André Stannek Jun 22 '17 at 16:05
@Thorsten-Kettner It works perfectly smooth!!! Appreciate your timely help.Thank you so very much.Very well done.You all guys are master, I never realized how important that order column was.I have just plugged your solution, but will go through it in the evening to understand it completely. – Rajiv A Jun 22 '17 at 18:18
Special thanks to all other participants for making me realize what I am missing! Thanks Again! – Rajiv A Jun 22 '17 at 18:19

Oracle Grouping by data value in a column

1 Answers1