All geek questions in one place

Difficult T-SQL to display an organization chart (hierarchy / recursion)

Please do not give me an article on how to create tree structures or CTEs in SQL that I read a lot! I think it may not be that difficult for t-sql at heart, but it is definitely difficult 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 of my stored procedure (SQL Server sproc) is set to “Everything”, since it just captures all the data, and the end user can expand / collapse the elements to see the hierarchy. The problem arises when, for example, I run a report and select a name, for example, in this case "Kevin Biking" sees the result:

alt text http://img69.imageshack.us/img69/8398/46964880.png

The problem with this is that I get a direct report on Kevin, but I really need to see all the sub-items. For example, in the first image, I would like to see in my report all people below kevin, and below kelvin and below Tim, etc. Etc.

I understand the problem, but I do not 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

sproc works fine, it has no errors, but my question is to use my fields, which I have listed here, how can I change it to receive direct reports under a different name, as I described above. Basically, the EmployeeName field is the top level each time (this is a report parameter), and the ReportsTo alias is the field in the report that you see in the image.

I have no question about the SSRS report, just how to modify the request so that in this case, if I select Kevin Bicking and pass it to my stored procedure. Currently, he returns only a direct employee of Kelvin Squires. But what I want to return is not only Kelvin, but all the people who report to Kelvin, and all the people who can be the boss under Kelvin, but also have direct reports.

Any help is greatly appreciated. Thank you for your time!

Change part

I am using sql server 2005. Someone asked for a table definition, please note that I did not create this table, this is an automatic generated CRM-based system:
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

Decision

With the help of you guys, it was my decision

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
+3
source share
2

EDIT OP:

OP, :

              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 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

:

@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)
+5

. MSDN , . . ( ):

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
+5

Source: https://habr.com/ru/post/1755987/

More articles:


All Articles