WITH ReportTree(father, sub_seq_no, sub_code, sub_name, sub_level, sub_path)
AS(
SELECT father, sub_seq_no, sub_code, sub_name,
1 AS sub_level, convert(nvarchar(max), replace(str(sort,4),' ','0')) AS sub_path
FROM REPORT_SETTING
WHERE father='0' AND acc_yr='100' AND report_type='1' AND in_use='1'
UNION ALL
SELECT rs.father, rs.sub_seq_no, rs.sub_code, rs.sub_name,
sub_level+1, sub_path+'.'+ convert(nvarchar(max), replace(str(rs.sort,4),' ','0'))
FROM REPORT_SETTING rs INNER JOIN ReportTree rt ON rs.father=rt.sub_seq_no
WHERE rs.acc_yr='100' AND rs.report_type='1' AND in_use='1'
)
SELECT sub_code AS 會計科目代碼,
space(sub_level*4)+sub_name AS 會計科目名稱, sub_seq_no, father, sub_level, sub_path
FROM ReportTree
ORDER BY sub_path, sub_seq_no
