CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Ryan Gomes
Ryan Gomes

Reputation: 75

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)

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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.

Upvotes: 1

Related Questions