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;