CODEWITHSUNDEEP
sqlsql-servert-sql
Donald B
Donald B

Reputation: 57

T-SQL Grouping With Splits

I want to group on consecutive like values. I want to group the first 3 Reg Unit, the one ICU and the last 3 Reg Unit. The problem is the 3rd group is the same as the first group and I'm unable to group them separately. I've tried window functions and other things to no avail. Would appreciate any ideas. Thanks.

Dataset

ID  OccpdID   Unit
1     1       Reg
1     2       Reg
1     3       Reg
1     4       ICU
1     5       Reg
1     6       Reg
1     7       Reg

Result to be:

ID  OccpdID Unit    Group
1     1     Reg      1
1     2     Reg      1
1     3     Reg      1
1     4     ICU      2
1     5     Reg      3
1     6     Reg      3
1     7     Reg      3

Upvotes: 2

Views: 64

Answers (3)

D-Shih
D-Shih

Reputation: 46219

You can try to use LAG in a subquery with SUMand CASE WHEN window function.

SELECT ID,OccpdID,Unit,SUM(CASE WHEN Unit <> prevVal THEN 1 ELSE 0 END) OVER(ORDER BY OccpdID) +1 as 'Group'
FROM (
    SELECT *, LAG(Unit) OVER(ORDER BY OccpdID) prevVal
    FROM Dataset
) t1

sqlfiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would use lag() and a cumulative sum:

select t.*,
       sum(case when prev_unit = unit then 0 else 1 end) over (order by occpdid) as grp
from (select t.*, lag(t.unit) over (order by t.occpdid) as prev_unit
      from t
     ) t;

Upvotes: 3

Chris Mack
Chris Mack

Reputation: 5208

I'm not sure how/if you want the ID to factor into it, but something like this will provide the desired results:

SELECT
    ID
    , OccpdID
    , Unit
    , SUM(CASE WHEN Unit <> L OR L IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY OccpdID) [Group]
FROM
    (
        SELECT
            ID
            , OccpdID
            , Unit
            , LAG(Unit, 1, NULL) OVER (ORDER BY OccpdID) L
        FROM YourTable
    ) Q

Upvotes: 3

Related Questions