T-SQL concatenate an incremental row number

: Joel Lipman; Transact-SQL; 24 May 2013; Hits: 9616

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:

copy raw

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

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

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:

copy raw

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)

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)

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:

copy raw

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

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

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:

copy raw

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

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

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:

copy raw

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