Blog ala Vidar

Some might panic now and think “OMG WTF does this mean LOL?!?!?!?!”. Let’s start with the second word (or acronym), CTE stands for Common Table Expressions and means that you can write something like a sub-select in T-SQL. An example would be:

WITH MyCTE AS
(
	SELECT TOP 1 *
		FROM Employees
)

SELECT *
	FROM MyCTE

This doesn’t make much sense, as you could just do:

SELECT TOP 1 * FROM Employees

But, if we add the other word, “Recursive”, which means doing something repeatedly, we might add some sense to it. First we’ll create a test table and put in some test data.

CREATE TABLE Employees
(
	ID TINYINT NOT NULL,
	Name VARCHAR(20) NOT NULL,
	ManagerID TINYINT NULL
)

INSERT INTO Employees
  (ID, Name, ManagerID)
SELECT 1, 'Petter', NULL UNION ALL
SELECT 2, 'Morten', 1 UNION ALL
SELECT 3, 'Trygve', 2 UNION ALL
SELECT 4, 'Vidar', 3 UNION ALL
SELECT 5, 'Håvard', 3 UNION ALL
SELECT 6, 'Stian', 5 UNION ALL
SELECT 7, 'Trine', 5 UNION ALL
SELECT 8, 'Werner', 4 UNION ALL
SELECT 9, 'Jakob', 2 UNION ALL
SELECT 10, 'Geir Ove', 9 UNION ALL
SELECT 11, 'Øystein', 10

As you see from this, Petter is the CEO. He’s only got one directly under him, and that’s Morten. Under Morten you’ve got Trygve, which has two directly under him: Vidar and Håvard. THIS IS JUST TEST DATA! Now that we got some test data, we can try to find out who’s got who as managers. We want it in the correct order, so that the hierarchy is correct. Petter first, then Morten, Trygve, Jakob. Then Trygve and Jakob’s employees. How to solve this? EASY!

WITH MyCTE (ID, Name, ManagerID, SortOrder) AS
(
	SELECT ID, Name, ManagerID, 0 AS SortOrder
		FROM Employees
		WHERE ManagerID IS NULL
	UNION ALL
	SELECT E.ID, E.Name, E.ManagerID, C.SortOrder + 1
		FROM Employees AS E
		INNER JOIN MyCTE AS C ON E.ManagerID = C.ID
)

SELECT *
	FROM MyCTE
	ORDER BY SortOrder, ManagerID

First we’re selecting out the one where ManagerID IS NULL, and then we’re recursively joining this and unioning it to show the output. Try it yourself and you’ll see for yourself!

Off topic… My new awesome background image, thanks to Werner: