StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Sounds like you want to use a recursive CTE. The <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="nofollow noreferrer">books online article</a> talks about your kind of scenario. Here's a sample set of code that I just used in a different stackoverflow article...</p> <pre><code>CREATE TABLE dbo.ctetest (employeeid int primary key not null, managerid int null); INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 1, NULL; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 2, 1; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 3, 1; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 4, 2; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 5, 2; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 6, 3; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 7, 2; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 8, 5; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 9, 4; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 10, 6; INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 11, 6; WITH recursivecte (employeeid, managerid, level) AS (SELECT employeeid , managerid , 'level' = 0 FROM dbo.ctetest WHERE managerid IS NULL UNION ALL SELECT ct.employeeid , ct.managerid , 'level' = rc.level + 1 FROM dbo.ctetest ct JOIN recursivecte rc ON ct.managerid = rc.employeeid) SELECT * FROM recursivecte rc </code></pre> <p>This should give you the hierarchy of each employee from level to level. If you want to return information about the next highest level such as a manager name, then you just need to add rc.managername to the second part of the UNION ALL, add columns to the CTE table (that's the WITH recursivecte (employeeid, managerid, level) section, and give place-holders in the first portion of the statement.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload