- asp.net mvc
- IT 관련
SQL 2005 이상, 재귀 ( Hierarchy ) 함수 CTE 활용해서 tree 구조 만들기 본문
SQL 2005 이상, 재귀 ( Hierarchy ) 함수 CTE 활용해서 tree 구조 만들기재우니 2013. 11. 9. 19:49
재귀함수를 사용한 내용이다. 아래 블로그에서 펌한 내용이지만,
트리 형태로 정확히 나오지 않아, Hierarchy 부분에,
숫자 4자리(없으면 앞에 0으로 채움) 를 만들어 정렬하는데 문제 없도록 했다.
WITH Recursive_CTE AS (
child.CompanyID, child.DeptID, child.ActiveDept,
CAST(child.NameBase as varchar(100)) NameBase,
CAST('>> ' as varchar(100)) LVL,
CAST(right('0000'+convert(varchar(4), child.DeptID), 4) as varchar(100)) Hierarchy
FROM DP_ACC_DEPT child
WHERE CompanyID = 1 AND PARENTDEPTID is null
CAST(LVL + child.NameBase as varchar(100)) AS NameBase,
CAST('>> ' + LVL as varchar(100)) AS LVL,
Hierarchy + ':' + CAST(right('0000'+convert(varchar(4), child.DeptID), 4) as varchar(100)) as varchar(100)
FROM Recursive_CTE parent
INNER JOIN DP_ACC_DEPT child ON child.CompanyID = parent.CompanyID AND child.ParentDeptID = parent.DeptID
SELECT * FROM Recursive_CTE
WHERE CompanyID = 1 AND ActiveDept = '1'
ORDER BY Hierarchy
QL Server Recursive Query structure is new with SQL Server 2005 Common Table Expression improvement. CTE (Common Table Expression) has a wide usage area in T-SQL programming. But what makes CTE indispensable in SQL development is its recursive query creation features.
An SQL Server recursive query is formed of three sections.
The anchor query within the recursive CTE query is the first section. Anchor query is the start up row for recursion. For example, in the anchor query you select the top level item. Or you select a specific row using the WHERE clause in the anchor query. Anchor query is the first row in the first recursion of the CTE expression.
Second part in SQL recursive CTE expression is a SELECT statement from the target table. This is generally the same table used in anchor SELECT. But this time it is INNER JOIN 'ed with the recursive CTE. The INNER JOIN condition identifies whether you are going to upper levels or you're querying to lower levels. This INNER JOIN expression set the parent/child relation between rows in the main sql table.
The result sets of the CTE inner sections are combined into a single return set using with UNION ALL expression
The last section is the SELECT statement which query CTE itself.
Assume that the company we are going to use in our SQL Server recursive query samples is named Adventure Works Cycle. And assume that the organizational structure of Adventure Works Cycle company is as follows.
As usual there are parent and child organizational units in the chart. These are parent/child rows in our database table where we will soon desing to store the company structure.
The OrganizationalStructures table is very simple in desing. It has a self-reference column ParentUnitID which refers to BusinessUnitID field of the one level upper organizational unit.
Right after the CREATE TABLE sql command, sample data for the above organizational chart is populated using SQL INSERT INTO command. We will use this sql table and data in it for SQL Server recursive query samples in this T-SQL tutorial.
Let's see what is our sample data looks like using SQL SELECT command.
In this SQL tutorial using Recursive CTE (Common Table Expression), sql programmers will soon be able to query sql data and return a list of business units in related with each other with parent/child properties
The following CTE - Common Table Expression is a sample of SQL Server Recursive Query.
The below SQL recursive query returns a list of rows from OrganizationalStructures which has BusinessUnitID equal to 1 and the sub-items of this anchor row.
Our first SQL Server Recursive CTE query returns all records in the table since the anchor select returns the top level item in the organization chart.
Of course, it is important to build reusable code in SQL just like other programming languages. TSQL developers can save the above SQL Server recursive query in a stored procedure by making simple modifications in the CTE expression in order to make it parametric.
We can change the anchor SELECT part of the inner CTE expression to alter the recursive query. As you see in the following t-sql code, the anchor query returns rows with BusinessUnitID equals to @BusinessUnitID stored procedure parameter value. And actually this is the all change required for creating parameteric recursive queries.
Right after the stored procedure is created, I executed the SP to return business units defined below Channel Marketing (direct or indirectly combined)
Let's modify the above SQL Server recursive query in order to add some detail information about the hierarchy and to give some visual effects as follows
The output of the above SQL Recursive CTE query will result as follows
I hope SQL developers will enjoy the SQL Server recursive query structure just as I do. For more SQL Server CTE (Common Table Expression) features, you can refer to T-SQL tutorial New Features in SQL Server 2005 - Common Table Expression (SQL CTE)
|MSSQL 파일 백업을 할때 압축 처리하는 방법 (0)||2014.10.23|
|OPENQUERY 를 이용한 insert ~ select 구문 (2)||2014.03.14|
|SQL 2005 이상, 재귀 ( Hierarchy ) 함수 CTE 활용해서 tree 구조 만들기 (0)||2013.11.09|
|MSSQL 의 XML TYPE 내부의 함수 알아보기 (0)||2013.09.24|
|저장프로시저의 동적쿼리 sql injection 공격 차단하기 (0)||2013.05.03|
|SQL 2005 이상 WITH 함수 JOIN 및 집합물을 처리 (0)||2013.04.23|