Increment by 1 by its group

Transact-SQL
1

Hi all,

I have the following product table

ID      Desc       
556     Prod A    
556     Prod A    
556     Prod A   
559     Prod C 
559     Prod C     
559     Prod C 
559     Prod C   
987     Prod J   
987     Prod J

I need a counter for each ID, and then concat for a NEW ID column as shown below.

ID      Desc       Line   NEW ID
556     Prod A     0      556-0
556     Prod A     1      556-1
556     Prod A     2      556-2
559     Prod C     0      559-0
559     Prod C     1      559-1
559     Prod C     2      559-2
559     Prod C     3      559-3
987     Prod J     0      987-0
987     Prod J     1      987-1

so if i were to add another 556 product, the line would look like:

556     Prod A     3      556-3

Any ideas?

2

something like this:

WITH cte
AS (
     SELECT id
          , [desc]
          , group_id
          , ROW_NUMBER() OVER (
               PARTITION BY id, [desc] 
               ORDER BY (SELECT 1)
               ) AS line_no
     FROM @
     )
UPDATE cte
SET group_id = line_no - 1
3

@gbritton perfect!!
thanks

4

Try this.

DECLARE @product table(
ID int, Descrp varchar (50));

insert into @product values(556, 'Prod A')
insert into @product values(556, 'Prod A')
insert into @product values(556, 'Prod A')
insert into @product values(559, 'Prod C')
insert into @product values(559, 'Prod C')
insert into @product values(559, 'Prod C')
insert into @product values(559, 'Prod C')
insert into @product values(987, 'Prod J')
insert into @product values(987, 'Prod J')

 SELECT ID
      , Descrp          
      , ROW_NUMBER() OVER (
           PARTITION BY [ID], [Descrp] 
           ORDER BY [ID] 
           ) -1 AS Line_No

       ,CONCAT(ID ,'-',ROW_NUMBER() OVER (
           PARTITION BY [ID], [Descrp] 
           ORDER BY [ID] 
           ) -1 ) AS 'NEW ID'
     
 FROM @product