Retrieving hierarchical table data as simple table format for report? Help please!

See more:

SQL

CrystalReports

CTE

, +

Dear experts,
I have a hierarchical table which stores data like below in SQL Server 2008 database.

ID	Description	Category	ParentID
1	ABC	       Objective	 NULL
2	CDE	       Objective	 NULL
3	XXX	       Objective	 NULL
4	YYY	       Criteria	     1
5	DDD	       Criteria	     1
6	AAA	       Criteria      2
7	CCC	       Procedure     4
8	EEEE	   Procedure     4
9	JJJJ	   Procedure     6

Now, I want to write a Stored procedure to retrieve the data from above Hierarchical Table in the format below (fig. 2):

Objective	Criteria	Procedure
ABC	         YYY	     CCC
ABC	         YYY	     ZZZ
CDE	         KKK	     RRRR
CDE	         DDD	     EEEE
XXX	         AAA	     JJJJ

for the purpose of REPORT.

EDITED:
I did a CTE Query Like below:

;WITH CteDAP ( ID, HierarchyID, Description, Category_Level, Depth)
	 AS
	 (
			SELECT DISTINCT 
					  A.ID, A.HierarchyID, A.Description, B.Name AS Category_Level,0 AS Depth
			FROM      tblHierarchy A INNER JOIN tblCat_lvl B ON A.fkCat_lvlId = B.pkCat_lvlId 
			WHERE     A.HierarchyID IS NULL
		UNION ALL 		
			SELECT    
					  C.ID, C.HierarchyID, C.Description, D.Name AS Category_Level, (CTE.Depth + 1) AS Depth
			FROM      tblHierarchy C INNER JOIN tblCat_lvl D ON C.fkCat_lvlId = D.pkCat_lvlId INNER JOIN
				  CteDAP CTE ON C.HierarchyID = CTE.ID
	 )
	 SELECT * FROM CteDAP ORDER BY Depth, Description

Got Result as :

ID HierarchyID	  Description	                    category_Level	    Depth
10	NULL	  Capital Grants…………	                  AOC	            0
11	NULL	  Contributions…………..	                  AOC	            0
15	NULL	  Current Grants …………	                  AOC	            0
20	10	      To ascertain that expenditure…………….	 Overall Objective	1
25	11	      To ascertain that other ……………………….	 Overall Objective	1
26	15	      To ascertain that pay ………………..	     Overall Objective	1
28	20	      To ascertain that …………….	             Sub Objective	    2
30	20	      Ascertain…………..	                     Sub Objective	    2
40	25	      Evaluation of …………….	                 Sub Objective	    2
42	28	      Received should ………..	                 Criteria	        3
43	28	      Received should be ………..	             Criteria	        3
45	40	      All supplies of …………	                 Criteria	        3
50	40	      Annual increments ………………	             Criteria	        3
53	42	      Check from the …………..	                 Procedure	        4
54	42	      Check whether ……………	                 Procedure	        4
59	50	      Check whether it………………	             Procedure	        4

Now I want the data in (fig. 2) format.
Any help will be highly appreciated. Thanks.

What I have tried:

I tried writing CTE but i could not achieve the format in what i wanted, dynamic POVIT may help me but don't know how to write a dynamic POVIT. Help Please!