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
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 :-)
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
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
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 :(
- Edited by Mark Pearce88 6 hours 35 minutes ago
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")
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