By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST( PID INT, PName VARCHAR(100), Counter INT)
INSERT INTO TEST VALUES(1 ,'Something1', 0)
INSERT INTO TEST VALUES(2 ,'Sub-Some', 1)
INSERT INTO TEST VALUES(3 ,'Sub-Some', 2)
INSERT INTO TEST VALUES(4 ,'Sub-Some', 3)
INSERT INTO TEST VALUES(5 ,'Sub-Some', 4)
INSERT INTO TEST VALUES(6 ,'Sub-Some', 5)
INSERT INTO TEST VALUES(7 ,'Something2', 0)
INSERT INTO TEST VALUES(8 ,'Sub-Some', 1)
INSERT INTO TEST VALUES(9 ,'Sub-Some', 2)
INSERT INTO TEST VALUES(10 ,'Sub-Some', 3)
INSERT INTO TEST VALUES(11 ,'Sub-Some', 4)
INSERT INTO TEST VALUES(12 ,'Sub-Some', 5)
INSERT INTO TEST VALUES(13 ,'Sub-Some', 6)
INSERT INTO TEST VALUES(14 ,'Something3', 0)
INSERT INTO TEST VALUES(15 ,'Sub-Some', 1)
INSERT INTO TEST VALUES(16 ,'Sub-Some', 2)
INSERT INTO TEST VALUES(17 ,'Something4', 0)
INSERT INTO TEST VALUES(18 ,'Sub-Some', 1)
INSERT INTO TEST VALUES(19 ,'Something5', 0)
19 rows affected
SELECT PID,PName,Counter,iif(Counter=0,'Product','SubProduct') typeProduct
,FIRST_VALUE(PName) OVER(PARTITION BY MRW ORDER BY PID) AS ParentPName
,FIRST_VALUE(PID) OVER(PARTITION BY MRW ORDER BY PID) AS ParentPID
FROM (
SELECT *,MAX(RW) OVER( ORDER BY PID) AS MRW
FROM (
SELECT *,
CASE WHEN Counter=0 THEN ROW_NUMBER() OVER(PARTITION BY Counter ORDER BY PID)
ELSE 0 END AS RW
FROM TEST
)A
)A
| PID | PName | Counter | typeProduct | ParentPName | ParentPID |
|---|---|---|---|---|---|
| 1 | Something1 | 0 | Product | Something1 | 1 |
| 2 | Sub-Some | 1 | SubProduct | Something1 | 1 |
| 3 | Sub-Some | 2 | SubProduct | Something1 | 1 |
| 4 | Sub-Some | 3 | SubProduct | Something1 | 1 |
| 5 | Sub-Some | 4 | SubProduct | Something1 | 1 |
| 6 | Sub-Some | 5 | SubProduct | Something1 | 1 |
| 7 | Something2 | 0 | Product | Something2 | 7 |
| 8 | Sub-Some | 1 | SubProduct | Something2 | 7 |
| 9 | Sub-Some | 2 | SubProduct | Something2 | 7 |
| 10 | Sub-Some | 3 | SubProduct | Something2 | 7 |
| 11 | Sub-Some | 4 | SubProduct | Something2 | 7 |
| 12 | Sub-Some | 5 | SubProduct | Something2 | 7 |
| 13 | Sub-Some | 6 | SubProduct | Something2 | 7 |
| 14 | Something3 | 0 | Product | Something3 | 14 |
| 15 | Sub-Some | 1 | SubProduct | Something3 | 14 |
| 16 | Sub-Some | 2 | SubProduct | Something3 | 14 |
| 17 | Something4 | 0 | Product | Something4 | 17 |
| 18 | Sub-Some | 1 | SubProduct | Something4 | 17 |
| 19 | Something5 | 0 | Product | Something5 | 19 |