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

Returning Sequence of Numbers

For quite sometime, I use COLORDER column of syscolumns table to return a sequential number. I will usually pick a table with large number of columns enough to return my requirement. Until I come across Common Table Expression (CTE). One of the major difference between a CTE and a derived table or sub-query, is that a CTE can be self-referencing or recursive.

Consider returning a series of numbers 1-31.

Here's the code:

WITH Number_Sequence
AS
(
   SELECT 1 AS sequence
   UNION ALL
   SELECT sequence+1
   FROM Number_Sequence W
      WHERE sequence < 31
)
SELECT *
FROM Number_Sequence

Now that it's a result set, it may be used to join to another table. Since this is a CTE and not a physical table, it's not indexed and could degrade processing. Table size should always be considered before joining this CTE to another table.

Here's another sample code that simulate bit positions in a byte:

with GroupNumbers
as
(
   select 1 as byte, 1 as from_bit, 8 as to_bit
   union all
   select byte + 1, from_bit + 8, to_bit + 7
   from GroupNumbers
   where to_bit < 64
)  select * from GroupNumbers

As always, comments are welcome...


~~CK