Levi Levi - 1 month ago 4
SQL Question

SQL - Append counter to recurring value in query output

I am in the process of creating an organizational charts for my company, and to create the chart, the data must have a unique role identifier, and a unique 'reports to role' identifier for each line. Unfortunately my data is not playing ball and it out of my scope to change the source.

I have two source tables, simplified in the image below. It is important to note a couple of things in the data.

  1. An employees manager in the query needs to come from the [EmpData] table. The 'ReportsTo' field is only in the [Role] table to be used when a role is vacant

  2. Any number of employees can hold the same role, but for simplicity lets assume that there will only ever be one person in the 'Reports to' role

Image of source tables and desired Query output

Using this sample data, my query is as follows:

/**Join Role table with employee data table.
/**Right join so roles with more than one employee will generate a row each
SELECT [Role].RoleId As PositionId
,[EmpData].ReportsToRole As ReportsToPosition
FROM [Role]
RIGHT JOIN [EmpData] ON [Role].RoleId=[EmpData].[Role]


/** Output all roles that do not have a holder, 'VACANT' in employee name.
SELECT [Role].RoleId
FROM [Role]

WHERE [Role].RoleID NOT IN (SELECT RoleID from [empdata])

This almost creates the intended output, but each operator roles has 'OPER', in the PositionId column.

For the charting software to work, each position must have a unique identifier.

Any thoughts on how to achieve this outcome? I'm specifically chasing the appended -01, -02, -03 etc. highlighted yellow in the Desired Query Output.


If you are using T-SQL, you should look into using the ROW_NUMBER operator with the PARTITON BY command and combining the column with your existing column.

Specifically, you would add a column to your select of ROW_NUMBER () OVER (PARTITION BY PositionID ORDER BY ReportsToPosition,EmployeeName) AS SeqNum

I would add that to your first query, and then, in your second, I would do something like SELECT PositionID + CASE SeqNum WHEN 1 THEN "" ELSE "-"+CAST(SeqNum AS VarChar(100)),...

There are multiple ways to do this, but this will leave out the individual ones that don't need a "-1" and only add it to the rest. The major difference between this and your scheme is it doesn't contain the "0" pad on the left, which is easy to do, nor would the first "OPER" be "OPER-1", they would simply be "OPER", but this can also be worked around.

Hopefully this gets you what you need!