CODEWITHSUNDEEP
sqlsql-serverxquery-sql
user2704193
user2704193

Reputation:

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

Upvotes: 1

Views: 44

Answers (2)

hchaznedaroglu
hchaznedaroglu

Reputation: 155

row_number() over (partition by ShopID)

You should also add an order by clause after partition by if you want your sequence numbers assigned in particular order.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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)

Upvotes: 2

Related Questions