재우니의 블로그


재귀함수를 사용한 내용이다.  아래 블로그에서 펌한 내용이지만,

트리 형태로 정확히 나오지 않아, Hierarchy   부분에,

숫자 4자리(없으면 앞에 0으로 채움) 를 만들어 정렬하는데 문제 없도록 했다.




WITH Recursive_CTE AS (

 SELECT

  child.CompanyID, child.DeptID, child.ActiveDept,

  CAST(child.NameBase as varchar(100)) NameBase,

  child.ParentDeptID,  

  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


 UNION ALL


 SELECT

  child.CompanyID, child.DeptID,child.ActiveDept,

  CAST(LVL + child.NameBase as varchar(100)) AS NameBase,

  child.ParentDeptID,  

  CAST('>> ' + LVL as varchar(100)) AS LVL,

  CAST(

Hierarchy + ':' + CAST(right('0000'+convert(varchar(4), child.DeptID), 4) as varchar(100)) as varchar(100)

) Hierarchy

  

 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





펌 : http://www.kodyaz.com/t-sql/sql-server-recursive-query-with-recursive-cte.aspx



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.

WITH RecursiveCTEQuery AS (
  {Anchor Query}
  UNION ALL
  {Query joined to RecursiveCTEQuery}
)
SELECT * FROM RecursiveCTEQuery

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.

SQL Server recursive query sample 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.

Create Table OrganizationalStructures (
 BusinessUnitID smallint identity(1,1),
 BusinessUnit varchar(100) Not Null,
 ParentUnitID smallint
)
insert into OrganizationalStructures values
('Adventure Works Cycle',NULL),
('Customer Care',1),
('Service',1),
('Channel Sales & Marketing',1),
('Customer Support',2),
('OEM Support',2),
('Central Region',3),
('Eastern Region',3),
('Western Region',3),
('OEM',4),
('Channel Marketing',4),
('National Accounts',4),
('Channel Field Sales',4),
('National Channel Marketing',11),
('Retail Channel Marketing',11),
('Central Region',13),
('Eastern Region',13),
('Western Region',13),
('Bicycles',15),
('Bicycle Parts',15)

Let's see what is our sample data looks like using SQL SELECT command.

select * from OrganizationalStructures

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

SQL Recursive query sample database table

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.

WITH Recursive_CTE AS (
 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  CAST(child.ParentUnitID as SmallInt) ParentUnitID,
  CAST(NULL as varchar(100)) ParentUnit
 FROM OrganizationalStructures child
 WHERE BusinessUnitID = 1

 UNION ALL

 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  child.ParentUnitID,
  parent.BusinessUnit ParentUnit
 FROM Recursive_CTE parent
 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID
)
SELECT * FROM Recursive_CTE

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.

SQL Server Recursive CTE query on Adventureworks

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.

CREATE PROCEDURE sp_ListOrganizationalStructuresOf (
 @BusinessUnitID smallint
)
AS
WITH Recursive_CTE AS (
 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  CAST(child.ParentUnitID as SmallInt) ParentUnitID,
  CAST(NULL as varchar(100)) ParentUnit
 FROM OrganizationalStructures child
 WHERE BusinessUnitID = @BusinessUnitID

 UNION ALL

 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  child.ParentUnitID,
  parent.BusinessUnit ParentUnit
 FROM Recursive_CTE parent
 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID
)
SELECT * FROM Recursive_CTE
GO
EXEC sp_ListOrganizationalStructuresOf 11

Right after the stored procedure is created, I executed the SP to return business units defined below Channel Marketing (direct or indirectly combined)

recursive CTE query sample in SQL Server

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

WITH Recursive_CTE AS (
 SELECT
  child.BusinessUnitID,
  CAST(child.BusinessUnit as varchar(100)) BusinessUnit,
  CAST(child.ParentUnitID as SmallInt) ParentUnitID,
  CAST(NULL as varchar(100)) ParentUnit,
  CAST('>> ' as varchar(100)) LVL,
  CAST(child.BusinessUnitID as varchar(100)) Hierarchy,
  1 AS RecursionLevel
 FROM OrganizationalStructures child
 WHERE BusinessUnitID = 1

 UNION ALL

 SELECT
  child.BusinessUnitID,
  CAST(LVL + child.BusinessUnit as varchar(100)) AS BusinessUnit,
  child.ParentUnitID,
  parent.BusinessUnit ParentUnit,
  CAST('>> ' + LVL as varchar(100)) AS LVL,
  CAST(Hierarchy + ':' + CAST(child.BusinessUnitID as varchar(100)) as varchar(100)) Hierarchy,
  RecursionLevel + 1 AS RecursionLevel
 FROM Recursive_CTE parent
 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID
)
SELECT * FROM Recursive_CTE ORDER BY Hierarchy

The output of the above SQL Recursive CTE query will result as follows

SQL Server recursion with CTE recursive query

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)