Tough T-SQL to display org chart (hierarchy / recursion)
Please do not point me to an article on how to create tree structures, or CTEs in SQL I've read plenty!!! I think this may not be so tough for the t-sql at heart but it is definitely tough for me :).
Here is the situation, I have to create a report that looks like this:
alt text http://img85.imageshack.us/img85/6372/70337249.png
This works great when the parameter to my stored procedure (SQL Server sproc) is set to 'All' as this just grabs all the data and the end user can expand / collapse items to see the hierarchy. The issue occurs when for instance I run the report and select a name such as in this case "Kevin Bicking" see the result:
alt text http://img69.imageshack.us/img69/8398/46964880.png
The issue with th开发者_开发百科is is I am only getting the direct report of kevin but I actually need to see all the sub directs. For instance in the first image I would want my report to display all of the people below kevin, and below kelvin and below Tim, etc etc.
I understand the issue but I don't know how to handle it in T-SQL. Here is my stored procedure:
CREATE PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
SET NOCOUNT ON;
SELECT
c1.id AS EmployeeID,
c2.id as ManagerID,
c1.first_name + ' ' + c1.last_name AS [EmployeeName],
c1.title AS Title,
c2.first_name + ' ' + c2.last_name AS [ReportsTo]
FROM
Contacts c1
INNER JOIN
Contacts c2
ON
c1.reports_to_id = c2.id
WHERE
c1.deleted=0
AND (@ContactID='All' OR (c2.first_name + ' ' + c2.last_name = @ContactID OR (c1.first_name + ' ' + c1.last_name = @ContactID)))
END
The sproc works fine, there is no error in it, but my question is using my fields that I have listed here how could I change it to get the direct reports under each other name as I have described above. Basically the field EmployeeName is the top level each time (that is the report parameter), the ReportsTo alias is the field on the report that you see in the image.
I do not have a question about the SSRS report, just about how to modify the query such that in this case if I select Kevin Bicking and pass that to my stored procedure. It currently only returns the direct employee Kelvin Squires. But what I want it to return is not only Kelvin, but all the people that report to Kelvin, and all the people that may be bosses under kelvin but also have direct reports.
Any help is greatly greatly appreciated. Thanks for your time!
Edit Portion
I am using sql server 2005. Somebody asked for a table definition, please note I did not create this table it is a CRM based system that is auto generated:USE [sugarcrm]
GO
/****** Object: Table [dbo].[contacts] Script Date: 07/22/2010 10:44:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[contacts](
[id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[date_entered] [datetime] NULL,
[date_modified] [datetime] NULL,
[modified_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_by] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[deleted] [bit] NULL DEFAULT ('0'),
[assigned_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[team_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[salutation] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[first_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[do_not_call] [bit] NULL DEFAULT ('0'),
[phone_home] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone_mobile] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone_work] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone_other] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone_fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_address_street] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_address_city] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_address_state] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_address_postalcode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_address_country] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_address_street] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_address_city] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_address_state] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_address_postalcode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_address_country] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assistant] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assistant_phone] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lead_source] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reports_to_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[birthdate] [datetime] NULL,
[portal_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[portal_active] [bit] NOT NULL DEFAULT ('0'),
[portal_password] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[portal_app] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[campaign_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [pk_contacts] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Solution
With the help of you guys here was my solution
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
SET NOCOUNT ON;
--grab id of @contactid
DECLARE @Test varchar(36)
SELECT @Test = (SELECT id FROM contacts c1 WHERE c1.first_name + ' ' + c1.last_name = @ContactID)
;WITH StaffTree AS
(
SELECT
c.id,
c.Title,
c.first_name,
c.last_name,
c.reports_to_id,
c.reports_to_id as Manager_id,
cc.first_name AS Manager_first_name,
cc.last_name as Manager_last_name,
cc.first_name + ' ' + cc.last_name AS [ReportsTo],
c.first_name + ' ' + c.last_name as EmployeeName,
1 AS LevelOf
FROM Contacts c
LEFT OUTER JOIN Contacts cc ON c.reports_to_id=cc.id
WHERE c.id=@Test OR (@Test IS NULL AND c.reports_to_id IS NULL)
UNION ALL
SELECT
s.id,
s.Title,
s.first_name,
s.last_name,
s.reports_to_id,
t.id,
t.first_name,
t.last_name,
t.first_name + ' ' + t.last_name,
s.first_name + ' ' + s.last_name,
t.LevelOf+1
FROM StaffTree t
INNER JOIN Contacts s ON t.id=s.reports_to_id
WHERE s.reports_to_id=@Test OR @Test IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree
END
EDIT based on OP's table:
here is an example using the columns from the OP's table definition, my sample data is as follows:
1-Jerome
|
2-Joe
/ \
3-Paul 6-David
/ \ / \
4-Jack 5-Daniel 7-Ian 8-Helen
--I only included the needed columns from the OP's table here
DECLARE @Contacts table (id varchar(36), first_name varchar(100), reports_to_id varchar(36))
INSERT @Contacts VALUES ('1','Jerome', NULL )
INSERT @Contacts VALUES ('2','Joe' ,'1')
INSERT @Contacts VALUES ('3','Paul' ,'2')
INSERT @Contacts VALUES ('4','Jack' ,'3')
INSERT @Contacts VALUES ('5','Daniel','3')
INSERT @Contacts VALUES ('6','David' ,'2')
INSERT @Contacts VALUES ('7','Ian' ,'6')
INSERT @Contacts VALUES ('8','Helen' ,'6')
DECLARE @Root_id char(4)
--get complete tree---------------------------------------------------
SET @Root_id=null
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
SELECT
c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
FROM @Contacts c
LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id
WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
UNION ALL
SELECT
s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
FROM StaffTree t
INNER JOIN @Contacts s ON t.id=s.reports_to_id
WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree
--get all below 2---------------------------------------------------
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
SELECT
c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
FROM @Contacts c
LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id
WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
UNION ALL
SELECT
s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
FROM StaffTree t
INNER JOIN @Contacts s ON t.id=s.reports_to_id
WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree
--get all below 6---------------------------------------------------
SET @Root_id=6
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
SELECT
c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
FROM @Contacts c
LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id
WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
UNION ALL
SELECT
s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
FROM StaffTree t
INNER JOIN @Contacts s ON t.id=s.reports_to_id
WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree
OUTPUT:
@Root_id=null
id first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1 Jerome NULL NULL NULL 1
2 Joe 1 1 Jerome 2
3 Paul 2 2 Joe 3
6 David 2 2 Joe 3
7 Ian 6 6 David 4
8 Helen 6 6 David 4
4 Jack 3 3 Paul 4
5 Daniel 3 3 Paul 4
(8 row(s) affected)
@Root_id='2 '
id first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2 Joe 1 1 Jerome 1
3 Paul 2 2 Joe 2
6 David 2 2 Joe 2
7 Ian 6 6 David 3
8 Helen 6 6 David 3
4 Jack 3 3 Paul 3
5 Daniel 3 3 Paul 3
(7 row(s) affected)
@Root_id='6 '
id first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
6 David 2 2 Joe 1
7 Ian 6 6 David 2
8 Helen 6 6 David 2
(3 row(s) affected)
Use a recursive query. MSDN's article on the subject uses an example that looks similar to yours. In your case, you would select Kevin's entry as the anchor definition. Try this (completely untested):
CREATE PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
WITH ManagerEmployee (ManagerID, EmployeeID, first_name, last_name, title)
AS
(
-- Anchor
SELECT ManagerID, EmployeeID, first_name, last_name, title
FROM Contacts
WHERE EmployeeID = @ContactID
UNION ALL
-- Recursion
SELECT ManagerID, EmployeeID, first_name, last_name, title
FROM Contacts c
JOIN ManagerEmployee me ON (me.EmployeeID = c.ManagerID)
)
SELECT ManagerID,
EmployeeID,
first_name + ' ' + last_name AS EmployeeName,
title as Title
FROM ManagerEmployee
END
加载中,请稍侯......
精彩评论