April 20, 2012 at 12:53 am
Hi,
Please use the following sql to create a sample of test data to play around with.
USE [master]
GO
--
--
-- CREATE DATABASE
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'HID')
BEGIN
CREATE DATABASE [HID];
END
GO
--
--
--
USE [HID]
GO
--
--
-- CREATE TABLE
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
hid HIERARCHYID NOT NULL,
lvl AS hid.GetLevel() PERSISTED,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(empid)
);
--
CREATE UNIQUE CLUSTERED INDEX idx_depth_first ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first ON dbo.Employees(lvl, hid);
--
--
-- CREATE STORED PROCEDURE
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
@empid AS INT,
@mgrid AS INT = NULL,
@empname AS VARCHAR(25),
@salary AS MONEY
AS
DECLARE
@hid AS HIERARCHYID,
@mgr_hid AS HIERARCHYID,
@last_child_hid AS HIERARCHYID;
IF @mgrid IS NULL
SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
-- Query the HIERARCHYID of the manager and store it
SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
-- Query the maximum HIERARCHYID value among the direct subordinates (i.e. immidiate children) of the manager and store it
SET @last_child_hid = (SELECT MAX(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid);
-- Call the GetDescendant method of HIERARCHYID for the manager's HIERARCHYID and get the HIERARCHYID for the last node and store it
SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END
--
INSERT INTO dbo.Employees(empid, hid, empname, salary)
VALUES(@empid, @hid, @empname, @salary);
GO
--
--
-- CREATE SAMPLE SET OF DATA
EXEC dbo.usp_AddEmp
@empid = 1, @mgrid = NULL, @empname = 'David' , @salary = $10000.00;
EXEC dbo.usp_AddEmp
@empid = 2, @mgrid = 1, @empname = 'Eitan' , @salary = $7000.00;
EXEC dbo.usp_AddEmp
@empid = 3, @mgrid = 1, @empname = 'Ina' , @salary = $7500.00;
EXEC dbo.usp_AddEmp
@empid = 4, @mgrid = 2, @empname = 'Seraph' , @salary = $5000.00;
EXEC dbo.usp_AddEmp
@empid = 5, @mgrid = 2, @empname = 'Jiru' , @salary = $5500.00;
EXEC dbo.usp_AddEmp
@empid = 6, @mgrid = 2, @empname = 'Steve' , @salary = $4500.00;
EXEC dbo.usp_AddEmp
@empid = 7, @mgrid = 3, @empname = 'Aaron' , @salary = $5000.00;
EXEC dbo.usp_AddEmp
@empid = 8, @mgrid = 5, @empname = 'Lilach' , @salary = $3500.00;
EXEC dbo.usp_AddEmp
@empid = 9, @mgrid = 7, @empname = 'Rita' , @salary = $3000.00;
EXEC dbo.usp_AddEmp
@empid = 10, @mgrid = 5, @empname = 'Sean' , @salary = $3000.00;
EXEC dbo.usp_AddEmp
@empid = 11, @mgrid = 7, @empname = 'Gabriel', @salary = $3000.00;
EXEC dbo.usp_AddEmp
@empid = 12, @mgrid = 9, @empname = 'Emilia' , @salary = $2000.00;
EXEC dbo.usp_AddEmp
@empid = 13, @mgrid = 9, @empname = 'Michael', @salary = $2000.00;
EXEC dbo.usp_AddEmp
@empid = 14, @mgrid = 9, @empname = 'Didi' , @salary = $1500.00;
EXEC dbo.usp_AddEmp
@empid = 15, @mgrid = 9, @empname = 'Brandon' , @salary = $1500.00;
EXEC dbo.usp_AddEmp
@empid = 16, @mgrid = 9, @empname = 'Arc' , @salary = $1500.00;
EXEC dbo.usp_AddEmp
@empid = 17, @mgrid = 9, @empname = 'George' , @salary = $1500.00;
EXEC dbo.usp_AddEmp
@empid = 18, @mgrid = 9, @empname = 'Deepak' , @salary = $1500.00;
Following query gives me the data in proper topological order.
SELECT * FROM Employees ORDER BY hid
But I would like is to select data in topological order as well as order by name within direct subordinates. So that would show following names under "Rita" in the order of name since they all are at the same level.
Emilia
Michael
Didi
Brandon
Arc
George
Deepak
Must be a Friday for me but I can't seem to make it work. Any help would be greatly appreciated.
TA 🙂
WRACK
CodeLake
April 20, 2012 at 5:07 am
This was removed by the editor as SPAM
April 22, 2012 at 3:28 am
I think the solution is not with the select but with your stored procedure usp_AddEmp. You need to insert the child with the appropriate HierarchyId. Possibly something like
-- CREATE STORED PROCEDURE
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
@empid AS INT,
@mgrid AS INT = NULL,
@empname AS VARCHAR(25),
@salary AS MONEY
AS
DECLARE
@hid AS HIERARCHYID,
@mgr_hid AS HIERARCHYID,
@child_hid1 AS HIERARCHYID,
@child_hid2 AS HIERARCHYID;
IF @mgrid IS NULL
SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
-- Query the HIERARCHYID of the manager and store it
SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
SET @child_hid1 = (SELECT MAX(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname < @empname );
SET @child_hid2 = (SELECT MIN(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname > @empname );
-- Call the GetDescendant method of HIERARCHYID for the manager's HIERARCHYID and get the appropriate HIERARCHYID and store it
SET @hid = @mgr_hid.GetDescendant(@child_hid1, @child_hid2);
END
--
INSERT INTO dbo.Employees(empid, hid, empname, salary)
VALUES(@empid, @hid, @empname, @salary);
GO
Dave
April 22, 2012 at 5:58 pm
Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2012 at 6:21 pm
Dave Brooking (4/22/2012)
I think the solution is not with the select but with your stored procedure usp_AddEmp. You need to insert the child with the appropriate HierarchyId. Possibly something like
-- CREATE STORED PROCEDURE
IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL
DROP PROC dbo.usp_AddEmp;
GO
CREATE PROC dbo.usp_AddEmp
@empid AS INT,
@mgrid AS INT = NULL,
@empname AS VARCHAR(25),
@salary AS MONEY
AS
DECLARE
@hid AS HIERARCHYID,
@mgr_hid AS HIERARCHYID,
@child_hid1 AS HIERARCHYID,
@child_hid2 AS HIERARCHYID;
IF @mgrid IS NULL
SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
-- Query the HIERARCHYID of the manager and store it
SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
SET @child_hid1 = (SELECT MAX(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname < @empname );
SET @child_hid2 = (SELECT MIN(hid) FROM dbo.Employees WHERE hid.GetAncestor(1) = @mgr_hid and empname > @empname );
-- Call the GetDescendant method of HIERARCHYID for the manager's HIERARCHYID and get the appropriate HIERARCHYID and store it
SET @hid = @mgr_hid.GetDescendant(@child_hid1, @child_hid2);
END
--
INSERT INTO dbo.Employees(empid, hid, empname, salary)
VALUES(@empid, @hid, @empname, @salary);
GO
Dave
Thanks Dave. I am afraid someone would come up with that answer. Appreciate it 🙂 I will try to implement it.
WRACK
CodeLake
April 22, 2012 at 6:23 pm
Jeff Moden (4/22/2012)
Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.
Sorry Jeff but I am not very familier with HierarchyID. Would be able to post alternatives to HierarchyID and still retrieve the data in the manner I want? TA 🙂
WRACK
CodeLake
April 22, 2012 at 11:56 pm
WRACK (4/22/2012)
Jeff Moden (4/22/2012)
Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.Sorry Jeff but I am not very familier with HierarchyID. Would be able to post alternatives to HierarchyID and still retrieve the data in the manner I want? TA 🙂
Ummm... Ok. I'm confused. You setup the HierarchyID datatype for your table and I thought that meant you were knowledgable with it especially since you have a proc to add employee's to it. Aren't you kind of stuck using it now?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 12:23 am
Almost forgot. Here's an article that explains one method to do as you ask.
http://www.sqlservercentral.com/articles/T-SQL/72503/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 1:15 am
I should have issued a disclaimer much the same as Jeff. I hadn't used HIERARCHYID before yesterday - we only use 2005 where I work.
However, based on Jeff's statement that the sorting is "ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.". Will the following work (I'm currently at work and don't have access to 2008 to test this)?
SELECT empid
,hid
,lvl
,empname
,salary
FROM dbo.Employees
ORDER BY hid.GetParent(1) ASC, empname ASC
Update: I'm sure it can't be that simple. I'm having difficulty visualising the hierarchyid values, I think the query given above will break the topological hierarchy structure.
Dave
April 23, 2012 at 2:53 am
April 23, 2012 at 4:30 am
Jeff Moden (4/22/2012)
WRACK (4/22/2012)
Jeff Moden (4/22/2012)
Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want is to do the ORDER BY by the HID of the ANCESTORs of the nodes you're displaying and then by the name of the node you're displaying.Sorry Jeff but I am not very familier with HierarchyID. Would be able to post alternatives to HierarchyID and still retrieve the data in the manner I want? TA 🙂
Ummm... Ok. I'm confused. You setup the HierarchyID datatype for your table and I thought that meant you were knowledgable with it especially since you have a proc to add employee's to it. Aren't you kind of stuck using it now?
I am not an expert on HierarchyID. Just trying to learn following Itzik's atricle. Apologies for confusion.
WRACK
CodeLake
April 23, 2012 at 3:58 pm
I believe this is Itzik's article http://www.sqlmag.com/content1/topic/hierarchyid/catpath/tsql3 to which WRACK refers in the previous post.
Using Jeff's article http://www.sqlservercentral.com/articles/T-SQL/72503/ a version using the supplied table with hierarchyid is
WITH
cteHierarchy AS
(
SELECT e.empid
,(SELECT e2.empid FROM dbo.Employees e2 WHERE e2.hid = e.hid.GetAncestor(1)) AS ManagerId
,e.empname
,lvl
FROM dbo.Employees e
),
cteDirectReports AS
(
SELECT e.empid
,e.ManagerId
,e.empname
,lvl = 1
,HierarchicalPath = CAST('\'+CAST(e.empname AS VARCHAR(10)) AS VARCHAR(4000))
FROM cteHierarchy e
WHERE e.ManagerId IS NULL
UNION ALL
SELECT e.empid
,e.ManagerId
,e.empname
,d.lvl + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.empname AS VARCHAR(10)) AS VARCHAR(4000))
FROM cteHierarchy e
INNER JOIN cteDirectReports d ON e.ManagerId = d.empid
)
SELECT *
FROM cteDirectReports
ORDER BY HierarchicalPath
;
Dave
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply