Print

T-SQL concatenate an incremental row number

What?
This is a quick article on how to use a counter added to the end of a field column of data.

Eh. What?
Assume the following two tables exist:
copyraw 
TABLE: Employee
--------------------------------------------------
ID     FirstName       Surname
1      Joel            Lipman
2      Another         Joe


TABLE: Qualifications
--------------------------------------------------
ID     Employee     Subject                 Grade
1      001          Computer Science BSc    2.1
2      001          Superman                10/10
3      002          Tape-Monkey Engineer    1:0
  1.  TABLE: Employee 
  2.  -------------------------------------------------- 
  3.  ID     FirstName       Surname 
  4.  1      Joel            Lipman 
  5.  2      Another         Joe 
  6.   
  7.   
  8.  TABLE: Qualifications 
  9.  -------------------------------------------------- 
  10.  ID     Employee     Subject                 Grade 
  11.  1      001          Computer Science BSc    2.1 
  12.  2      001          Superman                10/10 
  13.  3      002          Tape-Monkey Engineer    1:0 
So based on this logic, the Employee "Joel Lipman" has a degree in Computer Science BSc and some qualification in being Superman.

Specification
The output expected by the customer is the following:
copyraw 
EMP
--------------------------------------------------
EmpNumber     QualificationRef
001           00101
001           00102
002           00201

-- Qualification Ref is a unique number using the 
-- Employee Number concatenated with a 2 digit sequence 
-- number (padded with zeros)
  1.  EMP 
  2.  -------------------------------------------------- 
  3.  EmpNumber     QualificationRef 
  4.  001           00101 
  5.  001           00102 
  6.  002           00201 
  7.   
  8.  -- Qualification Ref is a unique number using the 
  9.  -- Employee Number concatenated with a 2 digit sequence 
  10.  -- number (padded with zeros) 

How?
So how do we do it? In the above example, Joel Lipman has two qualification records. So let's start with a simple query:
copyraw 
SELECT
    RIGHT('000' + CAST(e.ID AS VARCHAR), 3) AS EmployeeNo,
    RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) 
    + '01' AS QualificationRef
FROM 
    Qualifications q
INNER JOIN Employee e 
        ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee

-- yields
EmployeeNo      QualificationRef
--------------------------------
001             00101
001             00101
  1.  SELECT 
  2.      RIGHT('000' + CAST(e.ID AS VARCHAR), 3) AS EmployeeNo, 
  3.      RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) 
  4.      + '01' AS QualificationRef 
  5.  FROM 
  6.      Qualifications q 
  7.  INNER JOIN Employee e 
  8.          ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee 
  9.   
  10.  -- yields 
  11.  EmployeeNo      QualificationRef 
  12.  -------------------------------- 
  13.  001             00101 
  14.  001             00101 

Almost useful... but the qualification reference has to be unique. We're going to add the ROW_NUMBER() to the employee number to create a unique identifier. Try this:
copyraw 
SELECT
        q.Employee AS EmployeeNo,
        RIGHT('000' + CAST(q.Employee AS VARCHAR), 3)
        + 
        RIGHT(
                '00' 
                + 
                CAST(
                        (
                                ROW_NUMBER() OVER (ORDER BY q.Employee)
                        ) AS VARCHAR
                ), 2
        ) AS QualificationRef
FROM 
    Qualifications q
INNER JOIN Employee e 
        ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee

-- yields
EmployeeNo      QualificationRef
--------------------------------
001             00101
001             00102
002             00203     -- Wrong! This should start from 1 for another employee.
002             00204
003             00305
  1.  SELECT 
  2.          q.Employee AS EmployeeNo, 
  3.          RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) 
  4.          + 
  5.          RIGHT( 
  6.                  '00' 
  7.                  + 
  8.                  CAST( 
  9.                          ( 
  10.                                  ROW_NUMBER() OVER (ORDER BY q.Employee) 
  11.                          ) AS VARCHAR 
  12.                  ), 2 
  13.          ) AS QualificationRef 
  14.  FROM 
  15.      Qualifications q 
  16.  INNER JOIN Employee e 
  17.          ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee 
  18.   
  19.  -- yields 
  20.  EmployeeNo      QualificationRef 
  21.  -------------------------------- 
  22.  001             00101 
  23.  001             00102 
  24.  002             00203     -- Wrong! This should start from 1 for another employee. 
  25.  002             00204 
  26.  003             00305 

Almost There!
So we're using the ROW_NUMBER() functionality with an ORDER BY which seems to work somewhat. But using the code above still produces an error which can only be seen when using more records and more employees.

PARTITION BY
So a sort of "group" action will allow this if we add "PARTITION BY" to the ROW_NUMBER() statement:
copyraw 
SELECT
        q.Employee AS EmployeeNo,
        RIGHT('000' + CAST(q.Employee AS VARCHAR), 3)
        + 
        RIGHT(
                '00' 
                + 
                CAST(
                        (
                                ROW_NUMBER() OVER (
                                        PARTITION BY q.Employee 
                                        ORDER BY q.Employee)
                        ) AS VARCHAR
                ), 2
        ) AS QualificationRef
FROM 
    Qualifications q
INNER JOIN Employee e 
        ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee

-- yields
EmployeeNo      QualificationRef
--------------------------------
001             00101
001             00102
002             00201     -- Yay!
002             00202
003             00301
  1.  SELECT 
  2.          q.Employee AS EmployeeNo, 
  3.          RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) 
  4.          + 
  5.          RIGHT( 
  6.                  '00' 
  7.                  + 
  8.                  CAST( 
  9.                          ( 
  10.                                  ROW_NUMBER() OVER ( 
  11.                                          PARTITION BY q.Employee 
  12.                                          ORDER BY q.Employee) 
  13.                          ) AS VARCHAR 
  14.                  ), 2 
  15.          ) AS QualificationRef 
  16.  FROM 
  17.      Qualifications q 
  18.  INNER JOIN Employee e 
  19.          ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee 
  20.   
  21.  -- yields 
  22.  EmployeeNo      QualificationRef 
  23.  -------------------------------- 
  24.  001             00101 
  25.  001             00102 
  26.  002             00201     -- Yay! 
  27.  002             00202 
  28.  003             00301 
Category: Transact-SQL :: Article: 511