Reassign Sequential Code

Say I have this table:

if OBJECT_ID('tempdb..#SampleData') is not null
 drop table #SampleData
 
;with samples
as
(
   select 'ABC' as CID, 1 Code union all
   select 'ABC', 2 union all
   select 'ABC', 2 union all
   select 'XYZ', 1 union all
   select 'XYZ', 3 union all
   select 'XYZ', 3 union all
   select 'XYZ', 3
) 
select
*
into #SampleData
from samples

select * from #SampleData

CID    Code
---- ---------
ABC     1
ABC     2
ABC     2
XYZ     1
XYZ     3
XYZ     3
XYZ     3

Now I need to convert the CID for all those with Code > 1 into a sequential number and affix it to CID. The result will now look like this:

CID  code new_CID
---- ---- -------
ABC    1   ABC
ABC    2   ABC-01
ABC    2   ABC-02
XYZ    1   XYZ
XYZ    3   XYZ-01
XYZ    3   XYZ-02
XYZ    3   XYZ-03

My immediate reaction is to create a CTE to partion the result set to CID and sort it by code. Then handle the concatenation of the string on the outside call. Not a bad idea, actually.

However, I think in this case, a CTE is not necessary if I can just call the sorting directly.

Here's what I came up with:

select 
CID, code,
new_CID = CID + 
   case 
      when code = 1 then '' 
      else '-' + right('0' + cast(row_number() over(partition by CID order by CID, code) - 1 as varchar(2)),2) 
   end
from #SampleData

Here's the result set as required by the specs

CID  code  new_CID
---- ----- -------
ABC   1      ABC
ABC   2      ABC-01
ABC   2      ABC-02
XYZ   1      XYZ
XYZ   3      XYZ-01
XYZ   3      XYZ-02
XYZ   3      XYZ-03

As always, comments are welcome...


~~ CK