Sql Server Filter specific Row again and again

Question

I have a Table similar that

Product (3000 ROW)

PID	PName	Counter
1	Something1	0
2	Sub-Some	1
3	Sub-Some	2
4	Sub-Some	3
5	Sub-Some	4
6	Sub-Some	5
7	Something2	0
8	Sub-Some	1
9	Sub-Some	2
10	Sub-Some	3
11	Sub-Some	4
12	Sub-Some	5
13	Sub-Some	6
14	Something3	0
15	Sub-Some	1
16	Sub-Some	2
17	Something4	0
18	Sub-Some	1
19	Something5	0

brief explanation:

The employer agve me the data they have in Excel and I Re-input that like this in SQL

The problem is Counter employer doesn't separate Product and sub-Product

That means the one have Counter = 0 is Product and One have counter != 0 is Sub
**I don't have any Loop Problem **

I want to find and select only sub-product related to the product (like as PID = 1 is Product select Sub from 4 to 6)

I like To fix this problem in SQL server ... I even think to create new column to help separate them

I tried filtering by while Begin and End Break and ... what is wrong?

For given you all the information:
with "[HTTPGET] and where" I show the Client ProductName on the List and get the value of PID that Client Select then pass (the PID) to back and start from this **PID **show unit the next **Counter **0 (break)

I know my Big Question is About SSQL Query but i have a second small problem:
I can't Filter [HTTPPOST] or [HTTPPUT] to get only the PID not the hole info (i use fetch in react to send but can't better get the value on the Back side ... Any keyword to search _

I use SQL server & ASP.net Web API Core .net6 & React.JS

1. Get Sub Products of Product per Product (not all at one): SQL Server (Query)
2. Have answer or keyword to search "how to return one value with post or put with out create table

Answer 1

With this query, you can see the list of all sub products with the name code and father product code

First, the whole product was extracted, and then the children(sub products) were marked based on the father's code(product ), and finally, the father's(product ) name and code were placed next to him

typeProduct is type product (product,subproduct)

You can extract a list of children with a simple query

where typeProduct='Product' and ParentPName='Something1'

this is final code

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

this is fiddle: https://dbfiddle.uk/J2cVMKJG

result

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