Friday, 16 May 2014

Find the Parent-Child Relationship along with path and Generation in SQL SERVER

In this article we are going to see the parent and child relationship with Path formation and Generation, Let we take an example in UI we have an Tree control where we have hierarchy based on that we have to save the data in DB as well as retrieve the data, for this we have to find which is root of which element and how many elements are hierarchy under the elements.

Let we take an Example along with Table, consider the System Directory and starts with sample

Create Table

CREATE TABLE DIRECTORY
(
    ID INT IDENTITY(1,1),
      NAME VARCHAR(100),
      PARENTID INT

)

Insert Records


INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('d:',0)
INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Rajesh',1)
INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Sample',1)
INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('PDF',1)
INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Test',1)
INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('resume.doc',2)
INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Documents',3)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('c#',6)

Query 


;WITH FIR(ID,CHILD,PARENTID,GENERATION,PATH)
AS
(

SELECT ID,NAME,PARENTID,0,CAST(NAME AS VARCHAR)
FROM DIRECTORY
WHERE PARENTID = 0

UNION ALL

SELECT
REC.ID,REC.NAME,REC.PARENTID,FIR.GENERATION+1,CAST(FIR.PATH+'/'+REC.NAME AS VARCHAR)
FROM DIRECTORY AS REC
INNER JOIN FIR
ON REC.PARENTID = FIR.ID
)
SELECT CHILD,PARENTID,GENERATION,PATH
FROM FIR

ORDER BY PATH




Output:



From this article you can see how to get the parent child relationship.

No comments:

Post a Comment