Regu Regu - 3 months ago 6
SQL Question

SQL autocounter depending on a column value

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
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)
Comments