Databases
StackOverflow2013
Postsdbo
POTough T-SQL to display org chart (hierarchy / recursion)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTough T-SQL to display org chart (hierarchy / recursion)
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USoJM86o
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTough T-SQL to display org chart (hierarchy / recursion)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTough T-SQL to display org chart (hierarchy / recursion)
      1. USJonH
      1. VTFavorite
    3. VO
      singulars
      1. POTough T-SQL to display org chart (hierarchy / recursion)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhich version of SQL Server are you using?
      singulars
      1. POTough T-SQL to display org chart (hierarchy / recursion)
      1. USTom H
    2. CO@Tom H. Im using SQL Server 2005
      singulars
      1. POTough T-SQL to display org chart (hierarchy / recursion)
      1. USoJM86o
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload