TSQL Make partitions "gaps and island"-CodePudding

I need to create partitions. Suppose I have this table:

CREATE TABLE MyTable (Pos INT UNIQUE, X INT)
INSERT INTO MyTable VALUES (3, 2)
INSERT INTO MyTable VALUES (5, 0)
INSERT INTO MyTable VALUES (6, 0)
INSERT INTO MyTable VALUES (9, 0)
INSERT INTO MyTable VALUES (43, 9)
INSERT INTO MyTable VALUES (53, 8)
INSERT INTO MyTable VALUES (56, 0)
INSERT INTO MyTable VALUES (81, 0)
INSERT INTO MyTable VALUES (163, 1)
INSERT INTO MyTable VALUES (9716, 0)

The query result should be this table with a column Y added, Y should be

IF X=0 : the previous value X<>0 (OR NULL, if not exists), ordered by Pos

IF X<>0 : X

Desired answer table looks like this

SELECT * 
FROM MyQuery as a function of MyTable 
ORDER BY Pos

Pos  X  Y
3    2  2
5    0  2
6    0  2
9    0  2
43   9  9
53   8  8
56   0  8
81   0  8
163  1  1
9716 0  1

CodePudding user response：

This is a type of gaps-and-islands problem.

There are many solutions, here is one:

Use a running conditional count to number the rows that we want to group together
Use a partitioned conditional MIN to take the only value that we actually want, per group

WITH StartPoints AS (
    SELECT *,
      GroupId = COUNT(NULLIF(X, 0)) OVER (ORDER BY Pos)
    FROM MyTable
)
SELECT
  Pos,
  X,
  Y = MIN(NULLIF(X, 0)) OVER (PARTITION BY GroupId)
FROM StartPoints
ORDER BY Pos;

db<>fiddle