Ryan Gomes Ryan Gomes - 1 year ago 114
SQL Question

Dynamic Grouping

I would like to group my variables in a particular way

My data : blue column are IDs, red my data layout , green is my desired output

enter image description here

It groups all the A before the B then it Groups all the B before the next A And then groups the C

This is done for one ID, I would like this to happen for all Id( the information is dynamic for each id)

Answer Source

SQL tables represent unordered sets. There is no ordering to the rows unless a column explicitly specifies that ordering. Let me assume that such a column exists.

You want to identify groups and then bring them together. A simple way is to use lag() to determine where changes occur and then take the value when a change occurs:

select col
from (select t.*,
             lag(col) over (partition by id order by ??) as prev_col
      from t
     ) t
where prev_col is null or prev_col <> col;

The ?? is the column that specifies the ordering.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download