Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

'SQL Server Citation is a SQL Server blog by Hemantgiri S. Goswami, Surat,India. He is a SQL Server Specialist and SQL Server MVP,Author, President and Leader of Surat User Group
Browse by Tags · View All
Sql server 2008 14
sql 2005 10
sql 2008 10
sql server administration 10
sql server 2005 10
sql 2012 9
sql 2008 r2 9
sql server 2012 8
sql 2012 r2 6
sql 2000 6

Archive · View All
September 2011 9
March 2012 5
December 2011 4
January 2012 4
November 2011 3
April 2013 2
March 2013 2
October 2012 2
October 2011 2
December 2012 1

SQL Server Citation

SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL

Mar 27 2012 12:00AM by Hemantgiri S. Goswami   

Introduction

Today, I would like to explain one way in which we can store the HIERARCHICAL data in SQL tables. A general table structure which people come up to store this kind of data is -

1

Where, EmployeeID id the UniqueID alloted to every new employee record inserted into the table and ManagerID is the EmployeeID of the immediate manager of the employee. Keeping in mind that Manager is also an employee.

Problem Statement

This table structure very well serves the purpose as long as we have 1-Level hierarchy. However, if the hierarchy is of n'th level, the SELECT statement to fetch the records becomes more complex with this kind of table structure. Suppose, we want to fetch the complete TREE of a particular employee, i.e. list of all the employees who are directly or indirectly managed by a particular employee. How to do it……..?

Thanks to CTE’s for making the life a bit easier – as using them in a recursive manner, we can get the work done. Please follow this msdn link to see an implementation using recursive CTE.

Suggested Table Structure

2

Here, I have just included a new column [PATH]. It is of VARCHAR(MAX) type. I have taken it as VARCHAR(MAX) just to make sure the field is long enough to store the complete path. But one can assign appropriate size as per their system’s requirement.

The basic idea of the [path] column is to store the complete hierarchical path of any employee separated by a delimiter as under -

3

Calculating the new path is very simple. It’s just, {New Path} = {Parent Path} + {Self ID} + {Delimiter}

Now, suppose if I want to fetch all the employees who are directly or indirectly working under EmployeeID = 2, I can use the below tsql -

;WITH CTE AS (
SELECT 1 EmployeeID,NULL ManagerID, '\1\' [Path]
UNION ALL    
SELECT 2 EmployeeID,1 ManagerID, '\1\2\' [Path]
UNION ALL    
SELECT 3 EmployeeID,1 ManagerID, '\1\3\' [Path]
UNION ALL    
SELECT 4 EmployeeID,2 ManagerID, '\1\2\4\' [Path]
UNION ALL    
SELECT 5 EmployeeID,4 ManagerID, '\1\2\4\5\' [Path]
)
SELECT
  *
FROM
  CTE
WHERE
  [Path] LIKE '%\2\%'

We can use a simple logic to even find out the level of the Employee -


SELECT
  *,
  (LEN([Path]) - LEN(REPLACE([Path],'\',''))) - 2 [Level]
FROM
  CTE
WHERE
  [Path] LIKE '%\2\%'

4

2 is subtracted from the formula as the length of delimiter for Level-0 is 2.

Conclusion

Hope, this simple trick could save a lot of time for the ones who find themselves lost playing with the hierarchical data.


Republished from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP [15 clicks].  Read the original version here [9 clicks].

Hemantgiri S. Goswami
109 · 1% · 467
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL" rated 5 out of 5 by 1 readers
SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]