mssql 재귀쿼리 기본, 트리구조

제목 : mssql 재귀쿼리 기본, 트리구조

소스 :

use TEST

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)

select * From OrganizationalStructures

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

내용 :

예제 소스 파일 :

 출처 :