Hierachy by Christian Wade - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Hierachy by Christian Wade

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-05 : 21:17:18

[code]CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy')
INSERT INTO Employees VALUES(2 , 1 , 'Andrew')
INSERT INTO Employees VALUES(3 , 1 , 'Janet')
INSERT INTO Employees VALUES(4 , 1 , 'Margaret')
INSERT INTO Employees VALUES(5 , 2 , 'Steven')
INSERT INTO Employees VALUES(6 , 2 , 'Michael')
INSERT INTO Employees VALUES(7 , 3 , 'Robert')
INSERT INTO Employees VALUES(8 , 3 , 'Laura')
INSERT INTO Employees VALUES(9 , 3 , 'Ann')
INSERT INTO Employees VALUES(10, 4 , 'Ina')
INSERT INTO Employees VALUES(11, 7 , 'David')
INSERT INTO Employees VALUES(12, 7 , 'Ron')
INSERT INTO Employees VALUES(13, 7 , 'Dan')
INSERT INTO Employees VALUES(14, 11 , 'Jame')
[/code]
Her CTE query
[code]WITH EmpCTE(empid, empname, mgrid, depth, sortcol)
AS
(
-- anchor member
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM employees
WHERE empid = 1
UNION ALL
-- recursive member
SELECT E.empid, E.empname, E.mgrid, M.depth+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
-- outer query
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
[/code]
My question are how the M.dept will increase by digit and why convert to binary?what is the usage of binary

adopted from http://blogs.conchango.com/christianwade/archive/2004/11/09/234.aspx

AMITOFO+AMEN+YA ALLAH Hope the query works

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-05 : 23:03:13

need guideline to understand CTE >"<

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-05 : 23:56:04

change the outer query to


select	*
from	EmpCTE
ORDER BY sortcol

and check out the value of sortcol.

The purpose of sortcol is to sort the records in Hierarchy format from parent to child. Converting the empid to varbinary allow it to concatenante the parent empid with the child empid.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-06 : 01:33:28

but where the m.dept and sortcol come from?? why it suddenly appear?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 01:40:49

i don't see any m.dept column in there.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-06 : 01:47:17

quote:

SELECT E.empid, E.empname, E.mgrid, M.depth+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid

So sorry....i really don't understand i only ask...looking at it for few hour yest >"<

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 01:52:30

depth is just a counter. Starting with 0 in the anchor query, and increment by 1 in the recursive query section. The purpose of depth is to count the number of children for a parent.


WITH EmpCTE(empid, empname, mgrid, depth, sortcol)
AS
( 
  -- anchor member
  SELECT empid, empname, mgrid, 0,
    CAST(empid AS VARBINARY(900))
  FROM employees
  WHERE empid = 1
  UNION ALL
  -- recursive member
  SELECT E.empid, E.empname, E.mgrid, M.depth+1,
    CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)

Maybe you can also take a look at this
http://technet.microsoft.com/en-us/library/ms186243.aspx

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-06 : 03:32:41

i think i get it....or maybe don't...but anyway thx alot mr.tan...willing to explain other ppl article >"<

AMITOFO+AMEN+YA ALLAH Hope the query works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources