[sql]with tree

sql_with_tree.sql

WITH x AS (
SELECT [TYPE_SID]
      ,[CATEGORY_SID]
      ,[APARTMENT_SID]
      ,[TYPE_NAME]
      ,[REMARK]
      ,[PARENT_SID]
      ,[CREATEDBY]
      ,[CREATED_ON]
      ,[MODIFIEDBY]
      ,[MODIFIED_ON]
      ,[SORT_INDEX]
      ,[TAG]
      ,[LEVEL] = 0
  FROM [dbo].[HOME_SERVICE_TYPE] 
  WHERE PARENT_SID = '0' and APARTMENT_SID = '64884172-dc3c-4277-87a9-bfdeec040b13' and CATEGORY_SID = '9098ED29-072D-4653-A37D-3C2F6DF80861'
union all
SELECT t.[TYPE_SID]
      ,t.[CATEGORY_SID]
      ,t.[APARTMENT_SID]
      ,t.[TYPE_NAME]
      ,t.[REMARK]
      ,t.[PARENT_SID]
      ,t.[CREATEDBY]
      ,t.[CREATED_ON]
      ,t.[MODIFIEDBY]
      ,t.[MODIFIED_ON]
      ,t.[SORT_INDEX]
      ,t.[TAG] 
      ,[LEVEL] = x.[LEVEL] + 1
	FROM x inner join [dbo].[HOME_SERVICE_TYPE] t on t.PARENT_SID = x.TYPE_SID)
SELECT * FROM x order by LEVEL, [SORT_INDEX]