Link to home
Create AccountLog in
Avatar of dearnemo
dearnemo

asked on

SQL Programming

Hi,

Let's say I have a SQL table like this(this is actually a result of recursive CTE)

Employee         Manager              Level
Josh        Super Boss      1
Tom        Josh                       2
Mike        Josh                       2
John        Mike                      3
Mary        Mike                      3
Pam        Tom                      3
James        Tom                      3
Simon        Tom                      3
Sam        Pam                     4

How do I get the table like this:

Employee        Manager       Level                                     Final_Result
Josh      Super Boss        1                                       Super Boss
Tom      Josh                         2                                       Josh
Mike      Josh                         2                                       Josh
John      Mike                         3                                     Mike|Josh
Mary      Mike                         3                                     Mike|Josh
Pam      Tom                         3                                     Tom|Josh
James      Tom                         3                                     Tom|Josh
Simon      Tom                         3                                     Tom|Josh
Sam      Pam                         4                                     Pam|Tom|Josh

I have attached a screenshot also. Is this doable in SQL? Or is it doable using SQL CLR function? Please let me know how to do it. TIA.
After-Recursive-CTE-Function-Res.JPG
Avatar of edtechdba
edtechdba
Flag of United States of America image
This isn't fancy, but it should do the trick.

SELECT employee, manager, level,
(CASE WHEN COUNT(DISTINCT t.level) = 5 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 5) +
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 4)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 4 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 4)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 3 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 3)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2)
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 2 THEN
     (SELECT DISTINCT manager FROM table WHERE level = 2)
     ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 1 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 1)
END) AS final_result
FROM table t

Open in new window

Avatar of edtechdba
edtechdba
Flag of United States of America image
I just realized, that your desired result doesn't appear to match up with my code ..

Are you looking for this?
Final Result column (after = sign below):

Level 1 Manager = Level 1 Manager
Level 2 Manager = Level 2 Manager | Level 1 Manager
Level 3 Manager = Level 3 Manager | Level 2 Manager | Level 1 Manager
Level 4 Manager = Level 4 Manager | Level 3 Manager | Level 2 Manager | Level 1 Manager
... and so on.

You should be able to easily modify this code to display as needed (code posted above).
Avatar of dearnemo
dearnemo

ASKER

Hi edtechdba,

First of all thanks for your response. However I couldn't get your code working. Its throwing errors:
here's my code. I set up a table name as test.

SELECT Employee, Manager, [level],
(CASE WHEN COUNT(DISTINCT t.[level]) = 5 THEN
    (SELECT DISTINCT manager FROM test WHERE [level] = 5) +
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 4)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 3)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 2)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 1)
  WHEN COUNT(DISTINCT t.[level]) = 4 THEN
    (SELECT DISTINCT manager FROM test WHERE [level] = 4)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 3)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 2)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE level = 1)
  WHEN COUNT(DISTINCT t.[level]) = 3 THEN
    (SELECT DISTINCT manager FROM test WHERE [level] = 3)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 2)
    ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 1) 
  WHEN COUNT(DISTINCT t.[level]) = 2 THEN
     (SELECT DISTINCT manager FROM test WHERE [level] = 2)
     ' | ' + (SELECT DISTINCT manager FROM test WHERE [level] = 1) 
  WHEN COUNT(DISTINCT t.[level]) = 1 THEN
    (SELECT DISTINCT manager FROM test WHERE [level] = 1)
END) AS final_result
FROM test t

Open in new window


here are the SQL errors:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' | '.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ' | '.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ' | '.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WHEN'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ' | '.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ' | '.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ' | '.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WHEN'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ' | '.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ' | '.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'WHEN'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ' | '.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'WHEN'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ')'.

Open in new window


Questions-
1. First I want to make it work.
2. Then I want to somehow be able to set values for Level dynamically instead of hard coding. Is it possible?

Your question:
Are you looking for this?
Final Result column (after = sign below):

Level 1 Manager = Level 1 Manager
Level 2 Manager = Level 2 Manager | Level 1 Manager
Level 3 Manager = Level 3 Manager | Level 2 Manager | Level 1 Manager
Level 4 Manager = Level 4 Manager | Level 3 Manager | Level 2 Manager | Level 1 Manager
... and so on.
My Answer: YES.

Thanks a lot.
Avatar of edtechdba
edtechdba
Flag of United States of America image
Oops! I was missing + signs to concat the values.
Please try this code.

SELECT employee, manager, level,
(CASE WHEN COUNT(DISTINCT t.level) = 5 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 5) +
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 4) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 4 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 4)  + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 3 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 2 THEN
     (SELECT DISTINCT manager FROM table WHERE level = 2) + 
     ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 1 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 1)
END) AS final_result
FROM table t

Open in new window

Avatar of edtechdba
edtechdba
Flag of United States of America image
Yes, the counts can be set dynamically, just thought I'd give an example with hard coding to get the ball rolling. :)
Avatar of dearnemo
dearnemo

ASKER

Hi,

I just figured it out. I appended + signs. However it's complaining on
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'END'.

Open in new window


Thanks for your prompt response. First I want to make it work. Then we will figure out setting level dynamically. TIA.
Avatar of edtechdba
edtechdba
Flag of United States of America image
Added an ELSE in the CASE statement.

SELECT employee, manager, level,
(CASE WHEN COUNT(DISTINCT t.level) = 5 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 5) +
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 4) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 4 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 4)  + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 3 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 2 THEN
     (SELECT DISTINCT manager FROM table WHERE level = 2) + 
     ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 1 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 1)
ELSE
END) AS final_result
FROM table t

Open in new window

Avatar of dearnemo
dearnemo

ASKER

Its still complaining on that line. :(
Avatar of edtechdba
edtechdba
Flag of United States of America image
Err, looks like it should work! Added a blank value after ELSE.

You may also try commenting out code to see if you see where this error is coming from. It's late here and my eyes are starting to cross. :/

SELECT employee, manager, level,
(CASE WHEN COUNT(DISTINCT t.level) = 5 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 5) +
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 4) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 4 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 4)  + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 3 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 2 THEN
     (SELECT DISTINCT manager FROM table WHERE level = 2) + 
     ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 1 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 1)
ELSE ''
END) AS final_result
FROM table t

Open in new window

Avatar of PortletPaul
PortletPaul
Flag of Australia image
as your information is the result of a recursive CTE, why not just post that code?
it would be a lot simpler than "to-ing and fro-ing"

the CTE might perform some/all of this concatenation as it expands the tree too.
Avatar of dearnemo
dearnemo

ASKER

@edtechdba- I commented each when case but still couldn't get rid of the complaint on the last line. Are we missing an expression for ELSE case here? Or expression for ELSE case is not properly defined.?

@PortletPaul- Ok, here is my recursive CTE Code. Please suggest if I can tweak it to get the result I posted in my first post to this thread(Excel screenshot). Thanks.


With
	EmployeesCTE (EmployeeId, Name, ManagerId, [Level])
	as
	(
		select EmployeeId, Name, ManagerId, 1
		from tblEmployee where ManagerId is null
		
		UNION ALL
		
		select tblEmployee.EmployeeId, tblEmployee.Name,
		tblEmployee.ManagerId, EmployeesCTE.[Level] + 1
		from tblEmployee Join EmployeesCTE
		on tblEmployee.ManagerID = EmployeesCTE.EmployeeId
		)
		
		select EmpCTE.Name as Employee, ISNULL (mgrCTE.Name, 'Super Boss') as Manager,
		EmpCTE.[Level]
		from EmployeesCTE EmpCTE
		left join EmployeesCTE MgrCTE
		on EmpCTE.ManagerId = MgrCTE.EmployeeId

Open in new window

Avatar of edtechdba
edtechdba
Flag of United States of America image
And here's one last try with my "quick and easy" method (although it doesn't appear it has been anything but..)

Wrapping the THEN results in parentheses
SELECT employee, manager, level,
(CASE WHEN COUNT(DISTINCT t.level) = 5 THEN
   ( (SELECT DISTINCT manager FROM table WHERE level = 5) +
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 4) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1))
  WHEN COUNT(DISTINCT t.level) = 4 THEN
   ( (SELECT DISTINCT manager FROM table WHERE level = 4)  + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1))
  WHEN COUNT(DISTINCT t.level) = 3 THEN
   ( (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) )
  WHEN COUNT(DISTINCT t.level) = 2 THEN
    ( (SELECT DISTINCT manager FROM table WHERE level = 2) + 
     ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) )
  WHEN COUNT(DISTINCT t.level) = 1 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 1)
ELSE ''
END) AS final_result
FROM table t

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of dearnemo
dearnemo

ASKER

Hi,

I got this error:Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "mgrnm" of recursive query "EmployeesCTE".

Any idea? Thanks.
Avatar of PortletPaul
PortletPaul
Flag of Australia image
, cast('Super Boss' as varchar(50)) as mgrnm

This has to match the definition of [tblEmployee].[Name]
e.g. if that is (n)varchar(60) apply that to the super boss line above
Avatar of dearnemo
dearnemo

ASKER

@PortletPaul- Thanks a lot. It worked. It will take some time to undestand your queries and be bale to tweak it lil bit to suit my requirement. I might leave this thread open for sometime until I get all my doubts cleared out. I hope that's ok with you. You won my vote. :)

@: edtechdba- I tried your last post's query stil there seems an issue.
Msg 8120, Level 16, State 1, Line 1
Column 'test.Employee' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'test.Employee' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Open in new window

Avatar of edtechdba
edtechdba
Flag of United States of America image
Sorry about that ... if it is of any interest, I included the grouping.

SELECT employee, manager, level,
(CASE WHEN COUNT(DISTINCT t.level) = 5 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 5) +
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 4) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 4 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 4)  + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1)
  WHEN COUNT(DISTINCT t.level) = 3 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 3) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 2) + 
    ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 2 THEN
     (SELECT DISTINCT manager FROM table WHERE level = 2) + 
     ' | ' + (SELECT DISTINCT manager FROM table WHERE level = 1) 
  WHEN COUNT(DISTINCT t.level) = 1 THEN
    (SELECT DISTINCT manager FROM table WHERE level = 1)
ELSE ''
END) AS final_result
FROM table t
GROUP by employee, manager, level

Open in new window

Avatar of dearnemo
dearnemo

ASKER

Hi edtechdba,

I got the result but that's not right. Please see the screen attached to it.User generated image
Avatar of PortletPaul
PortletPaul
Flag of Australia image
>>I hope that's ok with you.
sure, no problem