Read Subject sub-child from tree structure in MS CRM 2011

Always its difficult to get the Subject Tree structure to implement your business logic.
I have written CTE to access the sub-child just by passing the Root level Subject.

Create the function in CRM database.

CREATE FUNCTION [dbo].[GetDownSubjectHierarchy] ( @SubjectId NVARCHAR(100))
RETURNS TABLE
AS
RETURN
(
WITH DirectReports (ParentSubject, ParentSubjectName, SubjectId, Title, Level)
AS
(
-- Anchor member definition
SELECT FSU1.ParentSubject,FSU1.ParentSubjectName, FSU1.SubjectId, FSU1.Title,0 AS Level
FROM dbo.Filteredsubject AS FSU1
WHERE SubjectId = @SubjectId

UNION ALL
-- Recursive member definition
SELECT FSU1.ParentSubject,FSU1.ParentSubjectName, FSU1.SubjectId, FSU1.Title, Level + 1
FROM dbo.Filteredsubject AS FSU1
INNER JOIN DirectReports AS d
ON FSU1.ParentSubject = d.SubjectId
)

    -- Statement that executes the CTE
    SELECT Distinct * FROM DirectReports d
)

Execute the below query to get the Subject sub-child of (down) level 2  by passing the subjectid.

SELECT * FROM [GetDownSubjectHierarchy]('35344367-8535-E211-AC03-000C29AD15D1')
WHERE Level=2

Just execute the Query and store the result in array/list/data set/data table as you want to execute your business logic.

You can get the sub-child of any level, just change the value of level (0 is the root subject).