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: 
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: 
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: 
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:
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: 
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