SQL query - Assign a Group Number to recods linked to every occurence of RecordType='DD'

Kumar 41 Reputation points
2021-12-06T17:15:29.797+00:00

Need help with below:

I have below kind of data.
CREATE TABLE #TEMP (ID INT, REcordType CHAR(2))
INSERT INTO #TEMP
SELECT 1, 'DD' UNION ALL
SELECT 2, 'G' UNION ALL
SELECT 3, 'DD' UNION ALL
SELECT 4, 'A' UNION ALL
SELECT 5, 'A' UNION ALL
SELECT 6, 'DD' UNION ALL
SELECT 7, 'Z' UNION ALL
SELECT 8, 'DD'

Need to find the occurrences of RecordType=-'DD' and assign a groupNumber for all rows whichever are before the beginning of next occurrence of next RecordType='DD' like below:

Looking for results like this:
ID RecordType GrpNum
1 DD Grp1
2 G Grp1
3 DD Grp2
4 A Grp2
5 A Grp2
6 DD Grp3
7 Z Grp3
8 DD Grp4

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,300 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,520 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 110K Reputation points
    2021-12-06T17:59:59.883+00:00

    Check a query:

    select *, concat('Grp', count(case RecordType when 'DD' then 0 end) over (order by ID)) GrpNum
    from #TEMP 
    order by ID
    
    0 comments No comments