Regu - 1 year ago 67

SQL Question

I am trying to create something like a sequence number that is iterating on each row by one and resetting when column shopID changes. But I would like to use a variable.

`select`

@Counter --what to do here?

,@data as Data

,case

when (ShopID.N.value('.', 'decimal(5,0)') = -1) then NULL

else ShopID.N.value('.', 'decimal(5,0)')

end as ShopID

from @xmlRes.nodes('/Root/ResponseData/response/shopItems') as S(N)

outer apply S.N.nodes('recordNumber') as ShopID(N)

I need to get something like this:

`Counter | Data | ShopID`

--------------------------------------

1 | Sample | 1

2 | Sample | 1

3 | Sample | 1

1 | Sample | 2

2 | Sample | 2

3 | Sample | 2

4 | Sample | 2

1 | Sample | 3

Answer Source

```
select
ROW_NUMBER() OVER (PARTITION BY ShopID ORDER BY Data) as Counter,
,@data as Data
,case
when (ShopID.N.value('.', 'decimal(5,0)') = -1) then NULL
else ShopID.N.value('.', 'decimal(5,0)')
end as ShopID
from @xmlRes.nodes('/Root/ResponseData/response/shopItems') as S(N)
outer apply S.N.nodes('recordNumber') as ShopID(N)
```