title: Tough T-SQL to display org chart (hierarchy / recursion)
body: <p>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 :).</p>
  
  <p>Here is the situation, I have to create a report that looks like this:</p>
  
  <p><a href="http://img85.imageshack.us/img85/6372/70337249.png" rel="nofollow noreferrer">alt text http://img85.imageshack.us/img85/6372/70337249.png</a></p>
  
  <p>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:</p>
  
  <p><a href="http://img69.imageshack.us/img69/8398/46964880.png" rel="nofollow noreferrer">alt text http://img69.imageshack.us/img69/8398/46964880.png</a></p>
  
  <p>The issue with this 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.</p>
  
  <p>I understand the issue but I don't know how to handle it in T-SQL.  Here is my stored procedure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Any help is greatly greatly appreciated. Thanks for your time!</p>
  
  <p><H1>Edit Portion</H1>
  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:</p>
  
  <pre><code>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
  </code></pre>
  
  <h1>Solution</h1>
  
  <p>With the help of you guys here was my solution</p>
  
  <pre><code>set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  go
  
  
  
  
  
  -- =============================================
  -- Author:      &lt;Author,,Name&gt;
  -- Create date: &lt;Create Date,,&gt;
  -- Description: &lt;Description,,&gt;
  -- =============================================
  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&gt;1 
  )
  SELECT * FROM StaffTree 
  
  END
  </code></pre>