Hierarchy
Hi,
I am working on creating a hierarchy which is a bit different

Like my table has following structure


deptid, deptmanager,empid,managerid,managername 
1         ABC,       3300, NULL, NULL
2         ABC,       3300, NULL,NULL 
3         CCC,       2000,,3300,ABC
4         DDD,       1200, 2000, CCC
5         EEE,       2211, 2000, CCC

I would like to create a result set like

EMPID,  DEPTID
3300     1
3300     2
3300     3
3300     4
3300     5
2000     3
2000     4
2000     5
1200     4
2211     5

I got all depts for emp id, 3300 because  the employees under this manager  belong to all dept

similarly for emp 2000, the child employees are 1200, 2211
therefore it shows 3 dept 3,4,5 [ its own dept and dept for child ]


Please guide

August 27th, 2015 3:08am

Good day Mark

Your database structure is not clear to me (this is nice way to say that you might need to think about improving the DDL or clarify it). As a first step please post DDL+DML instead of description your table as text. We can only guess without DDL (queries to create the table and relevant elements) which include indexes, constraints. etc'.

* It is look like you use deptid is the primary key in the table. "dep" sound to me like "department" (I HIGHLY RECOMMEND TO USE MEANING NAMES FOR THE TABLES). If so, this look like departments table. Why do you need deptmanager and managerid and managername columns on department table?!? these columns should be related to a manager. The departments table should include information regarding the departments entities. The manager is a different entity and should be in a different table (for example users table). For example you can have Users table with UserId as key, and in the departments table you can use foreign key to the Users table.

Anyway, regarding you request creating an hierarchy query, you can use hierarchy CTE as you can see and lean in this tutorial:

>> http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
>> https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

* again, for more specific help, please post DDL+DML (queries to create the table and queries to insert same sample data).

I hope this is useful :-)

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 3:38am

Well thanks a ton Actually I have used a dept/employee table as the most closest

exampleof what my actual table structure is. so plz just consider table and expected output declare @emptable TABLE( deptid int, deptmanager varchar(100), empid int , managerid int , managername varchar(100) ) insert into @emptable (deptid ,deptmanager,empid,managerid,managername ) values ('1', 'ABC', '3300', NULL, NULL) ('2', 'ABC', '3300', NULL,NULL ) ('3', 'CCC', '2000','3300','ABC') ('4', 'DDD', '1200', '2000', 'CCC') ('5', 'EEE', '2211', '2000', 'CCC') I would like to create a result set like EMPID, DEPTID 3300 1 3300 2 3300 3 3300 4 3300 5 2000 3 2000 4 2000 5 1200 4 2211 5 I got all depts for emp id, 3300 because the employees under this manager belong to all dept similarly for emp 2000, the child employees are 1200, 2211 therefore it shows 3 dept 3,4,5 [ its own dept and dept for child ] please dont go by the columns names or schema,its just a similar structure



August 27th, 2015 6:05am

Your data is not clear to me ;-(

>> I got all depts for emp id, 3300 because

who is that empid?!? I see two rows with the same empid:

('1',         'ABC',       '3300', NULL, NULL)
('2',         'ABC',       '3300', NULL,NULL )

is the third row related to the first row or to the second row?!?

The third row look like this:

('3',         'CCC',       '2000','3300','ABC')

is the 3000 came from the first row or the second row? what is the relation and what is the logic of the data structure? what is the logic of the result?

You asked e to ignore the column's names... OK but without this we have no logic of the data at all

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 3:57pm

Hi, Thanks for your reply and checking the request. Yes the dataset is bit confusing. we can consider it like that Empid 3300 is CEO and manages 2 departments directly under him ie dept 1,2 emp id 2000 is at the next level or can be any level

and he manages dept 3 directly. His manager is Empid-3300 if empid 2000 manages some other department directly ,

there can be more than one row for empid 2000 with manager id 3300 .

similarly empid 1200,2211 have manager as empid 2000 and they manage

departments 4 , 5 directly ..

Next for the output I need empid and the department [ which is directly

managed by that employee or managed by the employees under him ] example for employee 2000, i need dept 3, managed

by him and dept 4,5 managed by emp 1200,2211 ] This is quite a different requirement so i am stuck :(




August 27th, 2015 6:25pm

I really do not feel OK with posting the solution directly, but I will :-)

I will allow myself to say something before I post the solution, and I HIGHLY recommend you to read it in-depth.

The solution to what you asked is very simple once we understand your case, but the reason I feel conflict in posting it, is that I REALLY THINK THAT YOU SHOULD THINK ABOUT REDESIGNING YOUR DATABASE, and I know that most people that get solution, which work, will stop thinking about how it work or how to improve it or the consequences of using that solution. Most people think that "if it work, then we do not need to touch it", and I do not agree with this approach!

This approach leads people to wait to the catastrophic, before they try to fix it, but then it is too late :-(

Anyway, If I understood you currently, then this is the solution for your request:

use tempdb
GO

-------------------------------------- DDL
create table emptable (
	deptid int, deptmanager varchar(100),
	empid int ,
	managerid int ,
	managername varchar(100)
)

-------------------------------------- DML
insert into emptable (deptid ,deptmanager,empid,managerid,managername ) 
values
	('1',         'ABC',       '3300', NULL, NULL),
	('2',         'ABC',       '3300', NULL,NULL ),
	('3',         'CCC',       '2000','3300','ABC'),
	('4',         'DDD',       '1200', '2000', 'CCC'),
	('5',         'EEE',       '2211', '2000', 'CCC')

select * from emptable;

-------------------------------------- Solution
with MyCTE as (
	select empid,deptid,managerid,empid as F_empid , 1 as L 
	from emptable
	UNION ALL
	select e.empid,e.deptid,e.managerid,MyCTE.F_empid, MyCTE.L + 1
	from emptable e
	INNER JOIN MyCTE on e.managerid = MyCTE.empid
)
select distinct 
	deptid,F_empid--,empid,managerid,L
from MyCTE
order by F_empid desc,deptid
GO

I hope this is what you are looking for :-)
and I hope I succeed to make you think about re-thinking
("think about thinking is the first step before doing")

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 8:53pm

Hi Mark Pearce88,

It's possible! See the following code below:

declare  @MyTable table (
	deptid int, 
	deptmanager varchar(100),
	empid int ,
	managerid int ,
	managername varchar(100)
)

insert into @MyTable (deptid ,deptmanager,empid,managerid,managername ) 
values
	(1,'ABC',3300,NULL,NULL),
	(2,'ABC',3300,NULL,NULL),
	(3,'CCC',2000,3300,'ABC'),
	(4,'DDD',1200, 2000,'CCC'),
	(5,'EEE',2211, 2000,'CCC')

Select Distinct 
	EmpId,
	deptid
from
(
	Select 
		upv.deptid,
		case when isnumeric(upv.Orders)=0 then (Select top 1 empid from @MyTable t where t.deptmanager=deptmanager) else upv.Orders end as EmpId
	from
	(
		select 
			convert(varchar(4),deptid) as deptid,
			convert(varchar(4),managerid) as managerid,
			convert(varchar(4),empid) as empid,
			convert(varchar(4),managername) as managername,
			deptmanager
		from
			@MyTable
	) as q1
	unpivot(
		 Orders FOR value IN 
		  (empid, managerid,managername)

	) as upv
) as q2

order by deptid,EmpId

Result

Best Regards

August 27th, 2015 9:58pm

Many thanks an awesome solution
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 3:23am

Many thanks, yeah this was similar recurisive CTE  Thanks for suggesting for database changes

I completely agree with you .

Only this is this is one of the native dataset , poor ly designed and I am just a user.

Your blog is interesting 

Many thanks again

August 28th, 2015 3:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics