Sql-server – T-SQL: Assign identifier to block of results

querysql servert-sql

I'm not sure exactly how to phrase this question but… I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each group of a the same block value in the results returned by my query.

An example might be helpful! If I have a SQL statement such as

SELECT 
   Id, 
   Title, 
   Block, 
   Order 
FROM 
   TableName 
ORDER BY Order ASC

that returns the result:

Id Title BlockName Order
100 Title A Foo 1
101 Title B Foo 2
102 Title C Foo 3
103 Title D Bar 4
104 Title E Bar 5
105 Title F Foo 6

Is there any fairly straightforward way to generate a result which gives a unique id to each block of results, something like:

Id Title BlockName Order BlockGroup
100 Title A Foo 1 1
101 Title B Foo 2 1
102 Title C Foo 3 1
103 Title D Bar 4 2
104 Title E Bar 5 2
105 Title F Foo 6 3

I hope this is clear enough 🙂 Thanks in advance for any help!

Best Answer

Referencing SQL Server Lag Function to Group Table Rows on Column Value Changes:

--demo setup
drop table if exists Table1;
go

CREATE TABLE Table1
    ([Id] int, [Title] varchar(7), [BlockName] varchar(3), [Order] int)
;
    
INSERT INTO Table1
    ([Id], [Title], [BlockName], [Order])
VALUES
    (100, 'Title A', 'Foo', 1),
    (101, 'Title B', 'Foo', 2),
    (102, 'Title C', 'Foo', 3),
    (103, 'Title D', 'Bar', 4),
    (104, 'Title E', 'Bar', 5),
    (105, 'Title F', 'Foo', 6)
;

--solution
;WITH cte
AS (
    SELECT id
        ,Title
        ,BlockName
        ,[Order]
        ,lag(BlockName, 1, '') OVER (
            ORDER BY [Order]
            ) previous
        ,CASE 
            WHEN (
                    lag(BlockName, 1, '') OVER (
                        ORDER BY [Order]
                        )
                    ) = BlockName
                THEN 0
            ELSE 1
            END ischange
    FROM Table1
    )
    ,tbl
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM cte
            WHERE id <= t.id
            ) groupno
    FROM cte t
    )
SELECT Id
    ,Title
    ,BlockName
    ,[Order]
    ,groupno AS BlockGroup
FROM tbl

Id          Title   BlockName Order       BlockGroup
----------- ------- --------- ----------- -----------
100         Title A Foo       1           1
101         Title B Foo       2           1
102         Title C Foo       3           1
103         Title D Bar       4           2
104         Title E Bar       5           2
105         Title F Foo       6           3
Related Question