Question

I have a organization name table with the following structure given below:

CREATE TABLE [dbo].[DP_ORG_OrganizationUnit](
    [GID] [uniqueidentifier] NULL,
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Code] [nvarchar](100) NULL,
    [Name] [nvarchar](100) NULL,
    [LastUpdated] [datetime] NULL,
    [ManagedBy] [int] NULL, **SELF REFERENCING ID {For parent - child }***
    [Manager] [int] NULL,
 CONSTRAINT [PK_DP_ORG_OrganizationUnit] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Requirement is that I need to create a view that will give me a full path for each organization unit.

ID   CODE  NAME                    MANAGEDBY  MANAGER
1    HO    Head Office             0
2    IT    Information Technology  1
3.   FI    Finance                 1
4.   SP    IT Support              2
5.   M     Mergers                 3

I need the data to come as

1. Head Office
2. Head Office/Information Technology
3. Head Office/Finance
4. Head Office/Information Technology/IT Support
5. Head Office/Finance/Mergers

I want to directly pull this into a criteria form of a report so that the can select the sub departments from one single combo! How can I get this in any manner from TSQL(ms sql 2005). Thanks in advance for any solution.

Solution #1:

with cteAnchor as (
 select ID,CAST(Name as nvarchar(500)) as Name
 from DP_ORG_OrganizationUnit
 where ManagedBy  = 21)
, cteRecursive as 
 (select ID,CAST(Name as nvarchar(500))as Name
  from cteAnchor
 union all 
 select t.ID,CAST( r.Name +   /   + t.Name AS nvarchar(500))
 from DP_ORG_OrganizationUnit t
 join cteRecursive r on t.ManagedBy = r.ID)
select * from cteRecursive;

Answer 1

See Recursive Queries Using Common Table Expressions:

with cteAnchor as (
 select Name, id
 from DP_ORG_OrganizationUnit
 where ManagedBy  = 0)
, cteRecursive as (
 select id, Name
  from cteAnchor
 union all 
 select t.id, r.Name +  /  + t.Name
 from DP_ORG_OrganizationUnit t
 join cteRecursive r on t.ManagedBy = r.id)
select * from cteRecursive;

友情链接