Fetch the hierarchical Data with comma Separated......For Ex.How many Sub Department are there in one Department...that values are come with comma separated

Declare @ParentId int
set @ParentId = 171
Declare @OptionList varchar(max);
WITH

  cteReports (DepartmentID, Department, ParentDepartmentID)

  AS

  (

    SELECT DepartmentID, Department, ParentDepartmentID

    FROM HealthWatch..Department

    WHERE ParentDepartmentID = @ParentId

    UNION ALL

    SELECT d.DepartmentID, d.Department,d.ParentDepartmentID

     

    FROM HealthWatch..Department d

      INNER JOIN cteReports r

        ON d.ParentDepartmentID = r.DepartmentID

  )

SELECT @OptionList = coalesce(@OptionList+',' ,cast(@ParentId as varchar(255))+',')+ cast(DepartmentID as varchar(255))

FROM cteReports

print @OptionList