Select manager > employees - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select manager > employees

Author

Topic

Hayseed
Starting Member

6 Posts

Posted - 2009-01-29 : 17:32:34

I have been assigned one challenging query and am breaking it down into smaller tasks.

First task is: I need a select to show Managers, then any employees that report to them. Some of the employees are supervisors that have employees under them.

Here is my current query:


SELECT 
	p0.USER_NAME [Manager],
	p1.USER_NAME [UserLevel2],
	CASE WHEN p2.USER_NAME IS NULL THEN '' ELSE p2.USER_NAME END [UserLevel3],
	CASE 
		WHEN p2.USER_CONTRACTOR = 0 OR p1.USER_CONTRACTOR = 0 THEN 'E'
		WHEN p2.USER_CONTRACTOR = 1 OR p1.USER_CONTRACTOR = 1  THEN 'C'
	END [Employee/Contractor]
FROM T_USER p0
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p1 ON p1.USER_MANAGER_USER_FK = p0.USER_PK
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p2 ON p2.USER_MANAGER_USER_FK = p1.USER_PK
WHERE p0.USER_PK = 2005 
ORDER BY p1.USER_NAME, p2.USER_NAME

The data in the results is correct, but I am hoping for a different format.
Here is the current format:


Manager | UserLevel2 | UserLevel3 | Employee/Contractor
-------------------------------------------------------
Bill Smith | Jane Doe | Mark Jones | E
Bill Smith | Jane Doe | | E
Bill Smith | Mary Lambs | | C

The format I would like is:


Manager | UserLevel2 | UserLevel3 | Employee/Contractor
-------------------------------------------------------
Bill Smith | Jane Doe | Mark Jones | E
           | Jane Doe |            |E
           | Mark Lambs |          |C

Is this possible just using SQL?

token
Posting Yak Master

133 Posts

Posted - 2009-01-29 : 18:18:26

Highly doubtful that you can format the data that way. You need some kind of GROUP BY clause. I will look into it.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-29 : 18:50:53

So are you trying to output nulls/empty strings for the manager in your 2nd and 3rd rows? Sounds like a bad idea to me. What you're trying to do is done easily in SSRS or most other reporting platforms though.

Some days you're the dog, and some days you're the fire hydrant.

Hayseed
Starting Member

6 Posts

Posted - 2009-01-30 : 09:28:55

I have to use the custom in-house report writer and there are limitations on how I can format.

I have done alot of research on Google and found nothing that would work. If I can not have the format I was hoping for, I can use the original format.

Just trying to score a few points with management

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:40:08

quote:
Originally posted by Hayseed

I have to use the custom in-house report writer and there are limitations on how I can format.

I have done alot of research on Google and found nothing that would work. If I can not have the format I was hoping for, I can use the original format.

Just trying to score a few points with management

doesnt it have much formatting options? something like discard duplicates or remove duplicates for table cell? thats the property which gives you similar result in ssrs

Hayseed
Starting Member

6 Posts

Posted - 2009-01-30 : 09:58:36

quote:
Originally posted by visakh16
doesnt it have much formatting options? something like discard duplicates or remove duplicates for table cell? thats the property which gives you similar result in ssrs

No, it does not have any format options other than a tabular report or a chart report. The output of the SQL results are pretty much the only format you have. That is why I was hoping for some kind of solution that could produce the desired results in SQL.

It's a limitation of the tools I have to use.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 10:09:40

then use this rather ugly solution:-


SELECT CASE WHEN Seq=1  THEN [Manager] ELSE '' END,[UserLevel2],[UserLevel3],[Employee/Contractor]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p0.USER_NAME,p1.USER_NAME ORDER BY p1.USER_NAME DESC) AS Seq,
	p0.USER_NAME [Manager],
	p1.USER_NAME [UserLevel2],
        p2.USER_NAME,
	CASE WHEN p2.USER_NAME IS NULL THEN '' ELSE p2.USER_NAME END [UserLevel3],
	CASE 
		WHEN p2.USER_CONTRACTOR = 0 OR p1.USER_CONTRACTOR = 0 THEN 'E'
		WHEN p2.USER_CONTRACTOR = 1 OR p1.USER_CONTRACTOR = 1  THEN 'C'
	END [Employee/Contractor]
FROM T_USER p0
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p1 ON p1.USER_MANAGER_USER_FK = p0.USER_PK
	LEFT JOIN (SELECT USER_NAME, USER_MANAGER_USER_FK, USER_PK, USER_CONTRACTOR FROM T_USER) p2 ON p2.USER_MANAGER_USER_FK = p1.USER_PK
WHERE p0.USER_PK = 2005 
)t
ORDER BY [UserLevel2], USER_NAME

Hayseed
Starting Member

6 Posts

Posted - 2009-01-30 : 11:16:40

Thanks - that's much closer to what I need and should work just fine.

As I build more details (esp. the pivot) into the query and if I have other questions - should I start a new thread or
continue with this one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 11:57:27

if its related post here...else start new thread

Hayseed
Starting Member

6 Posts

Posted - 2009-01-30 : 12:22:56

OK - thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 12:26:21

welcome