April 12, 2015 at 2:26 am
Hi all,
how do i arrange the department and sections if i have a below table structure and data
table
dept_id Dept_Name Sec_id
1 A1 0
2 A2 1
3 B1 1
4 A3 2
5 A4 2
6 B2 1
7 B3 5
8 B4 5
9 B5 6
10 A5 3
output
dept_id dept_name
1 A1
2 A2
4 A3
5 A4
7 B3
8 B4
3 B1
10 A5
6 B2
9 B5
in simple ways i want to achieve parent and child relationship in the departments.
any help is highly appreciated.
regards
April 12, 2015 at 5:06 am
Quick solution utilizing a recursive CTE, fine for small sets but I suggest you have a look at Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url] and Joe Celko's Trees and Hierarchies in SQL for Smarties
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_DEPARTMENT_HIERARCHY') IS NOT NULL DROP TABLE dbo.TBL_DEPARTMENT_HIERARCHY;
CREATE TABLE dbo.TBL_DEPARTMENT_HIERARCHY
(
dept_id INT NOT NULL CONSTRAINT PK_DBO_TBL_DEPARTMENT_HIERARCHY_DEPT_ID PRIMARY KEY CLUSTERED
,Dept_Name VARCHAR(10) NOT NULL CONSTRAINT UNQ_DBO_TBL_DEPARTMENT_HIERARCHY_DEPT_NAME UNIQUE
,Sec_id INT NULL CONSTRAINT FK_DBO_TBL_DEPARTMENT_HIERARCHY_SEC_ID_DBO_TBL_DEPARTMENT_HIERARCHY_DEPT_ID
FOREIGN KEY REFERENCES dbo.TBL_DEPARTMENT_HIERARCHY(dept_id)
);
INSERT INTO dbo.TBL_DEPARTMENT_HIERARCHY(dept_id,Dept_Name,Sec_id)
VALUES
(1 ,'A1',NULL)
,(2 ,'A2',1)
,(3 ,'B1',1)
,(4 ,'A3',2)
,(5 ,'A4',2)
,(6 ,'B2',1)
,(7 ,'B3',5)
,(8 ,'B4',5)
,(9 ,'B5',6)
,(10,'A5',3);
;WITH DEP_HIERARCHY AS
(
SELECT
TDH.dept_id
,TDH.Dept_Name
,TDH.Sec_id
,1 AS LEVEL
,CONVERT(VARCHAR(500),TDH.Dept_Name + '\',0) AS HIER_STR
FROM dbo.TBL_DEPARTMENT_HIERARCHY TDH
WHERE TDH.Sec_id IS NULL
UNION ALL
SELECT
TDH.dept_id
,TDH.Dept_Name
,TDH.Sec_id
,DH.LEVEL + 1
,CONVERT(VARCHAR(500),DH.HIER_STR + CONVERT(VARCHAR(10),TDH.Dept_Name + '\',0),0) AS HIER_STR
FROM DEP_HIERARCHY DH
INNER JOIN dbo.TBL_DEPARTMENT_HIERARCHY TDH
ON DH.dept_id = TDH.Sec_id
)
SELECT
DH.dept_id
,DH.Dept_Name
,DH.Sec_id
,DH.LEVEL
,DH.HIER_STR
FROM DEP_HIERARCHY DH;
;
Results
dept_id Dept_Name Sec_id LEVEL HIER_STR
----------- ---------- ----------- ----------- --------------
1 A1 NULL 1 A12 A2 1 2 A1\A23 B1 1 2 A1\B16 B2 1 2 A1\B29 B5 6 3 A1\B2\B510 A5 3 3 A1\B1\A54 A3 2 3 A1\A2\A35 A4 2 3 A1\A2\A47 B3 5 4 A1\A2\A4\B38 B4 5 4 A1\A2\A4\B4
April 12, 2015 at 12:41 pm
Borrowing on Eirikur's test data, the Hierarchical Path and the hLevel can be used to format the output a bit (look at the Dept_Name column) and sort the hierarchy as you might expect like the following...
dept_id Sec_id Dept_Name hLevel HierarchicalPath
----------- ----------- -------------------- ----------- --------------------
1 NULL A1 1 \A1
2 1 A2 2 \A1\A2
4 2 A3 3 \A1\A2\A3
5 2 A4 3 \A1\A2\A4
7 5 B3 4 \A1\A2\A4\B3
8 5 B4 4 \A1\A2\A4\B4
3 1 B1 2 \A1\B1
10 3 A5 3 \A1\B1\A5
6 1 B2 2 \A1\B2
9 6 B5 3 \A1\B2\B5
Here's the code to do that...
WITH
cteDeptHierarchy AS
(
SELECT dept_id, Sec_id, Dept_Name, hLevel = 1,
HierarchicalPath = CAST('\'+CAST(Dept_Name AS VARCHAR(10)) AS VARCHAR(8000))
FROM dbo.TBL_DEPARTMENT_HIERARCHY
WHERE Sec_id IS NULL
UNION ALL
SELECT e.dept_id, e.Sec_id, e.Dept_Name, hLevel = d.hLevel + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Dept_Name AS VARCHAR(10)) AS VARCHAR(8000))
FROM dbo.TBL_DEPARTMENT_HIERARCHY e
INNER JOIN cteDeptHierarchy d ON e.Sec_id = d.dept_id
)
SELECT dept_id,
Sec_id,
Dept_Name = CAST(SPACE((hLevel-1)*2) + Dept_Name AS VARCHAR(20)), --CAST for formatting only
hLevel,
HierarchicalPath = CAST(HierarchicalPath AS VARCHAR(20)) --CAST for formatting only
FROM cteDeptHierarchy
ORDER BY HierarchicalPath
;
To find out more about how the code works, see the following article.
http://www.sqlservercentral.com/articles/T-SQL/72503/
As Eirikur also suggested, if you end up with more than just a few rows in your hierarchy, consider the use of "hybrid" hierarchies that offer the best of Adjacency Lists, Hierarchical Paths, and Nested Sets. For a "How to" there, please see the following article.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
Also, consider NOT using "TBL_" to prefix table names nor all caps for table names unless your whole server is case sensitive. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply