이리저리 참고문서 찾아다니면서 아래와 같은 쿼리로 원하는 결과를 얻긴했습니다.

하위 쿼리보다 좋은방식이 있으면 조언부탁드립니다.

DECLARE @TREE TABLE(
 
 ID   VARCHAR(10)
 ,PARENT_ID VARCHAR(10)
 ,CNT  INT
 ,RGDATE  VARCHAR(10)
)

INSERT INTO @TREE VALUES('1','', 1, '2014-01-01')
INSERT INTO @TREE VALUES('2','1', 2, '2014-01-08')
INSERT INTO @TREE VALUES('3','1', 3, '2014-02-11')
INSERT INTO @TREE VALUES('4','1', 1, '2014-04-11')
INSERT INTO @TREE VALUES('5','', 3, '2014-02-11')
INSERT INTO @TREE VALUES('6','2', 2, '2014-08-01')
INSERT INTO @TREE VALUES('7','2', 4, '2014-07-25')
INSERT INTO @TREE VALUES('8','', 1, '2014-04-27')
INSERT INTO @TREE VALUES('9','5', 5, '2014-01-01')
INSERT INTO @TREE VALUES('10','5', 9, '2014-01-01')

;WITH TBL_PARENT AS
(
 SELECT ID, PARENT_ID, CNT, RGDATE,
               CONVERT(VARCHAR(MAX), CAST(ID AS VARCHAR(MAX)) + '_') AS P_PATH
    FROM @TREE
    WHERE PARENT_ID = ''
 UNION ALL
 SELECT A.ID, A.PARENT_ID, A.CNT, A.RGDATE,
               CONVERT(VARCHAR(MAX), B.P_PATH + CAST(A.ID AS VARCHAR(MAX)) + '_') AS P_PATH
    FROM @TREE  A
 INNER JOIN TBL_PARENT B
 ON A.PARENT_ID = B.ID
)

SELECT
 SUBSTRING(P_PATH, 1, CHARINDEX('_', P_PATH) -1) As ID
 ,SUM(CNT) As TOTAL_COUNT
 ,MAX(RGDATE) As LAST_DATE
FROM TBL_PARENT
GROUP BY SUBSTRING(P_PATH, 1, CHARINDEX('_', P_PATH) -1)
ORDER BY SUBSTRING(P_PATH, 1, CHARINDEX('_', P_PATH) -1)