CODEWITHSUNDEEP
sql-server
lucky
lucky

Reputation: 1

Set RowNumber by grouping the value if next value is blank

I'm using Sql Server 2008. I want to group the Data by a condition and set the RowNumber for it. None of the columns in the table are unique values.

Grouping Condition

If Any Row occurred as Blank, group the previous not null rows and set Row Number for it.

Example:

My table Data looks like this:

Code    Name    Location

Db1     Name1   N 
Db3     Name3   S
NULL    NULL    NULL
Db1     Name1   N
NULL    NULL    NULL
Db1     Name1   N
NULL    NULL    NULL
Db1     Name1   S
Db4     Name4   S

I need the OUTPUT like this:

Sno     Code    Name    Location

1       Db1     Name1   N
1       Db3     Name3   S
2       Db1     Name1   N
3       Db1     Name1   N
4       Db1     Name1   S
4       Db4     Name4   S

Upvotes: 0

Views: 85

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

There is no such thing as the next row without having a column to ORDER BY. So I added an id as identity to solve this:

DECLARE @t table(id int identity(1,1), Code char(3),  Name char(5), Location char(1))

INSERT @t values
('Db1','Name1','N'),('Db3','Name3','S'),(NULL,NULL,NULL),
('Db1','Name1','N'),(NULL,NULL,NULL),('Db1','Name1','N'),
(NULL,NULL,NULL),('Db1','Name1','S'),('Db4','Name4','S')

;WITH cte as
(
  SELECT 
    row_number() over (order by id)
    - row_number() over (order by id * case when code is not null then 1 end) x, 
    Code, Name, Location
FROM @t
)
SELECT 
  dense_rank() over (order by x) Sno,
  Code, Name, Location
FROM cte 
WHERE code is not null

Result:

Sno Code  Name   Location
1   Db1   Name1  N
1   Db3   Name3  S
2   Db1   Name1  N
3   Db1   Name1  N
4   Db1   Name1  S
4   Db4   Name4  S

Upvotes: 2

Related Questions