T-SQL resistente para exibir o organograma (hierarquia / recursão)

Question

Jul 22, 2010, 03:36 PM

sql-server hierarchical-data sql-server-2005

T-SQL resistente para exibir o organograma (hierarquia / recursão)

Por favor, não me aponte para um artigo sobre como criar estruturas em árvore, ou CTEs em SQL, que eu já li bastante !!! Eu acho que isso pode não ser tão difícil para o t-sql no coração, mas é definitivamente difícil para mim :).

Aqui está a situação, eu tenho que criar um relatório parecido com este:

texto alternativo http://img85.imageshack.us/img85/6372/70337249.png

Isso funciona muito bem quando o parâmetro para o meu procedimento armazenado (SQL Server sproc) é definido como 'All', pois apenas captura todos os dados e o usuário final pode expandir / recolher itens para ver a hierarquia. O problema ocorre quando, por exemplo, eu executo o relatório e seleciono um nome, como neste caso "Kevin Bicking", veja o resultado:

texto alternativo http://img69.imageshack.us/img69/8398/46964880.png

O problema com isso é que estou recebendo apenas o relatório direto de Kevin, mas na verdade preciso ver todas as sub-direções. Por exemplo, na primeira imagem, eu gostaria que meu relatório exibisse todas as pessoas abaixo de Kevin, abaixo de Kelvin e abaixo de Tim, etc.

Entendo o problema, mas não sei como lidar com isso no T-SQL. Aqui está o meu procedimento armazenado:

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

O sproc funciona bem, não há nenhum erro nele, mas minha pergunta está usando meus campos que listei aqui, como eu poderia alterá-lo para obter os relatórios diretos sob o outro nome, como descrevi acima. Basicamente, o campo EmployeeName é o nível superior a cada vez (que é o parâmetro do relatório), o alias ReportsTo é o campo no relatório que você vê na imagem.

Não tenho uma pergunta sobre o relatório do SSRS, apenas sobre como modificar a consulta, de modo que, neste caso, se eu selecionar Kevin Bicking e a passar para o meu procedimento armazenado. Atualmente, ele retorna apenas o funcionário direto Kelvin Squires. Mas o que eu quero que retorne não é apenas Kelvin, mas todas as pessoas que se reportam a Kelvin, e todas as pessoas que podem ser chefes do Kelvin, mas também têm subordinados diretos.

Qualquer ajuda é muito apreciada. Obrigado pelo seu tempo!

Editar parteEstou usando o sql server 2005. Alguém pediu uma definição de tabela, observe que eu não criei esta tabela, é um sistema baseado em CRM que é gerado automaticamente:

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

Solução

Com a ajuda de vocês aqui foi a minha solução

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