Concatenate a count for each duplicate in dataset?-CodePudding

I want to add a concatenated count for each occurrence of a value in a dataset from query.

Example dataset:

ID      OtherValue    
00A     2ndValue_1    
00A     2ndValue_2    
00A     2ndValue_3    
00A     2ndValue_4    
00B     2ndValue_1    
00C     2ndValue_1    
00D     2ndValue_1    
00D     2ndValue_2

I wand the query to produce this dataset:

ID      OtherValue    ConcatinatedCount
00A     2ndValue_1    00A-001
00A     2ndValue_2    00A-002
00A     2ndValue_3    00A-003
00A     2ndValue_4    00A-004
00B     2ndValue_1    00B-001
00C     2ndValue_1    00C-001
00D     2ndValue_1    00D-001
00D     2ndValue_2    00D-002

I am not quite sure how to get this count per row.

CodePudding user response：

Window Functions (also called Analytics Functions or Ordered Analytics Functions) are the way to solve this.

SELECT 
  ID, 
  OtherValue, 
  ID   '-'   FORMAT(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY OtherValue), '000') as ConcatinatedCount
FROM yourtable;

That ROW_NUMBER() window-funtion will generate a row number within the partition/group of each distinct ID and it will order that row number by OtherValue within the group. Some concatenation and formatting and it should spit out what you are after.