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

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.