This one was over my head, I need to work on my recursive cte understanding. At any rate I always learn from your videos even when i don't fully understand the final solution.
I think u used a lengthy and confusing approch- my soln would be - with recursive cte as ( select employee ,manager, row_number() over () as x from company where manager = (select employee from company where manager is null) union all select d.employee, d.manager, x from cte c join company d on c.employee = d.manager ) select concat('team',x) as teams, group_concat(manager separator ',') as members from (select manager , x from cte union select employee,x from cte )y group by x order by x;
Great solution, I am thinking to solve this without using recursive function as I know for small hierarchy self join would work but there is too many hierarchy it doesn't. What is your thought in this?
@vikramjitsingh6769 bro I have done just like yours but modify few things can you please tell me is that okay regards with recursive cte as ( select employee ,manager, concat('team ',row_number() over (order by employee)) as rn from company where manager = (select employee from company where manager is null)
union all
select d.employee,d.manager,rn from cte join company d on cte.employee = d.manager)
,final_cte as ( select rn as teams, string_agg(employee,',') as emp from cte group by rn ) select teams, concat(employee,',',emp) as MEMBERS from company c cross join final_cte where c.manager is null ORDER BY teams
I modified this query to sql server , but error message says Msg 462, Level 16, State 1, Line 1 Outer join is not allowed in the recursive part of a recursive common table expression 'cte'. How to resolve this?
Mysql solution: with recursive cte as (select employee, manager, concat("Team ", row_number() over (order by employee) ) as team from company where manager in (select employee from company where manager is null) union all select co.employee, co.manager, cte.team from cte join company co where cte.employee = co.manager), cte2 as (select team,(select employee from company where manager is null) as manager, group_concat(employee) as people from cte group by team) select Team,concat(manager,",",people) as people from cte2;
with recursive cte as ( select manager,employee,row_number() over (order by employee) as rn from company where manager=(select employee from company where manager is null) union select b.manager,b.employee, rn from cte join company b on cte.employee=b.manager
) select concat ('Team ', rn),concat((select employee from company where manager is null),',',string_agg(employee,',')) from cte group by 1 order by 1
Can you also solve it by splitting them at their level , like assistant managers in a group , managers in a group , all senior consultants in one row , all jr consultants in one row ?
Hi, your source code brings following error, do you check the source code before you offer this to the public? sg 102, Level 15, State 1, Procedure spChallange#12, Line 25 [Batch Start Line 7] Incorrect syntax near 'cte'. Msg 102, Level 15, State 1, Procedure spChallange#12, Line 39 [Batch Start Line 7] Incorrect syntax near ','.
Do you think I post garbage script to public ? If you have followed my videos, you would know that the scripts given is for PostgreSQL database and I had mentioned in a previous video when it comes to recursive query in MS SQL Server database, you need to make slight syntactical changes.
With CTE as (select * ,'Elon' as xxx from company), CTE2 as (select manager, case when manager!='Elon' then concat(employee,',',xxx) end as employees from CTE where manager is not null) select manager,string_agg(employees,',') as Team from CTE2 group by 1
Sir the above solution works only when we add new teams in the table, if there is another hierarchy it will not give results. I write solution of above query it might works for that problem too team(please correct me sir if I'm wrong) MS Sql --new hierarchy insert into company values ('david',null); insert into company values ('bill','david'); select * from company; with root as( select employee from company where manager is null) , teams as( select concat('team ',row_number() over ( partition by root.employee order by (select null)),' of_',root.employee) team , c.employee, root.employee as manager from company c inner join root on root.employee=c.manager) ,rec as( select team,employee,manager from teams union all select rec.team, c.employee, c.manager from rec inner join company c on rec.employee=c.manager ) select team, string_agg(employee, ',') members from (select team, employee from rec union select team,manager from rec) k group by team Order by team;
Here is my solution with cte as( SELECT c1.*, c2.manager as second_manager FROM company c1 join company c2 on c1.manager = c2.employee), cte2 as ( select cte.*, c.manager as third_manager from cte join company c on cte.second_manager = c.employee),cte3 as( select STRING_AGG(employee,',') employees, manager, second_manager,third_manager from cte2 group by manager, second_manager,third_manager) ,cte4 as( select employees, manager, second_manager,coalesce(third_manager,'') third_manager from cte3 where cte3.employees not in (select manager from cte3)) select concat('Team ',row_number() over(order by manager)) Teams, concat(third_manager,',',second_manager,',', manager,',',employees) as members from cte4
MS SQL: WITH cte_teams AS ( SELECT mng.employee, CONCAT('Team ', ROW_NUMBER() OVER (ORDER BY mng.employee)) AS teams FROM company root JOIN company mng ON root.employee = mng.manager WHERE root.manager IS NULL ), cte AS ( SELECT c.employee, c.manager, t.teams FROM company c JOIN cte_teams t ON c.employee = t.employee UNION ALL SELECT c.employee, c.manager, cte.teams FROM company c JOIN cte ON c.manager = cte.employee ) SELECT teams, (SELECT employee FROM company WHERE manager IS NULL) + ', ' + STRING_AGG(employee, ', ') AS members FROM cte GROUP BY teams ORDER BY teams;
here is my solution with recursive cte as (select 'team'||row_number()over(order by c2.employee) as teams, c1.employee e1, c2.employee e2 from company c1 join company c2 on c1.employee = c2.manager where c1.manager is null), cte2 as (select teams, e1, e2 from cte union select cte2.teams,cte2.e1, c3.employee e2 from cte2 join company c3 on cte2.e2 = c3.manager), cte3 as (select teams, e1,string_agg(e2,',') members from cte2 group by 1,2)
select teams, (e1||','||members) as team from cte3 order by 1
with cte as ( SELECT c1.employee as E1, c2.employee as E2,c3.employee as E3,c3.manager as M1 FROM company c1 cross join company c2 on c1.manager=c2.employee cross join company c3 on c2.manager=c3.employee AND C1.EMPLOYEE NOT IN (select distinct MANAGER from company WHERE MANAGER IS NOT NULL) ), CTE2 AS (select group_concat(E1) AS EE,E2,E3,M1 from cte GROUP BY E2,E3,M1) SELECT CONCAT("Team ",row_number() OVER()) as Teams ,CASE WHEN M1 IS NULL THEN CONCAT(e3,",",e2,",",EE) else concat(m1,",",e3,",",e2,",",EE) end AS MEMBERS FROM CTE2 ORDER BY LENGTH(MEMBERS) desC;
ms sql approach with a as ( select c2.employee, c2.manager, 1 hier, ROW_NUMBER() over(order by c2.employee) team from company c1 join company c2 on c2.manager=c1.employee where c1.manager is null union all select company.employee, company.manager, hier+1 hier, a.team from a join company on a.employee=company.manager ), b as ( select employee, team, ROW_NUMBER() over(partition by team order by hier) rn from ( select manager employee, team, 0 hier from a where hier = 1 union all select employee, team, hier from a ) t ) select concat('team',team) team, string_agg(employee, ',') WITHIN GROUP (ORDER BY rn) members from b group by team
This one was over my head, I need to work on my recursive cte understanding. At any rate I always learn from your videos even when i don't fully understand the final solution.
this solution just bounced right over my head
I think u used a lengthy and confusing approch- my soln would be - with recursive cte as (
select employee ,manager, row_number() over () as x from company where manager = (select employee from company where manager is null)
union all
select d.employee, d.manager, x from cte c join company d on c.employee = d.manager )
select concat('team',x) as teams, group_concat(manager separator ',') as members from (select manager , x from cte union select employee,x from cte
)y group by x order by x;
Great solution, I am thinking to solve this without using recursive function as I know for small hierarchy self join would work but there is too many hierarchy it doesn't. What is your thought in this?
bro to be honest I was trying to solve using the video method but having trouble and found your solution it was very easy to understand thanks bro
@vikramjitsingh6769 bro I have done just like yours but modify few things can you please tell me is that okay regards
with recursive cte as (
select employee ,manager, concat('team ',row_number() over (order by employee)) as rn
from company
where manager = (select employee from company where manager is null)
union all
select d.employee,d.manager,rn
from cte
join company d
on cte.employee = d.manager)
,final_cte as (
select
rn as teams,
string_agg(employee,',') as emp
from cte
group by rn
)
select
teams,
concat(employee,',',emp) as MEMBERS
from company c
cross join
final_cte
where c.manager is null
ORDER BY teams
@@muhammadabbas6645 yep it works 👍
you did a great job bro👍.
You have made with great logic, I revised 2 times to get the logic... but this problem is very challenging
I modified this query to sql server ,
but error message says Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the recursive part of a recursive common table expression 'cte'. How to resolve this?
As always best 😊
In DB2 joins are not allowed inside recursive cte
what is the solution we made 200 tables updated in sql server replication got blocks
Mysql solution:
with recursive cte as (select employee, manager, concat("Team ", row_number() over (order by employee) ) as team from company
where manager in (select employee from company where manager is null)
union all
select co.employee, co.manager, cte.team from cte join company co
where cte.employee = co.manager),
cte2 as (select team,(select employee from company where manager is null) as manager, group_concat(employee) as people from cte
group by team)
select Team,concat(manager,",",people) as people from cte2;
19:16 use of Coalesc() is not clear to me.
with recursive cte as (
select manager,employee,row_number() over (order by employee) as rn from company
where manager=(select employee from company where manager is null)
union
select b.manager,b.employee, rn from cte join company b on cte.employee=b.manager
)
select concat ('Team ', rn),concat((select employee from company where manager is null),',',string_agg(employee,',')) from cte group by 1
order by 1
This problem is so hard, I didn't think about the recursive... Instead, I used multiple cte to solve the problem haha
Thankyou so much
Can you also solve it by splitting them at their level , like assistant managers in a group , managers in a group , all senior consultants in one row , all jr consultants in one row ?
Thank you so much sir
Hi, your source code brings following error, do you check the source code before you offer this to the public?
sg 102, Level 15, State 1, Procedure spChallange#12, Line 25 [Batch Start Line 7]
Incorrect syntax near 'cte'.
Msg 102, Level 15, State 1, Procedure spChallange#12, Line 39 [Batch Start Line 7]
Incorrect syntax near ','.
Do you think I post garbage script to public ?
If you have followed my videos, you would know that the scripts given is for PostgreSQL database and I had mentioned in a previous video when it comes to recursive query in MS SQL Server database, you need to make slight syntactical changes.
With CTE as
(select * ,'Elon' as xxx from company),
CTE2 as
(select manager,
case when manager!='Elon' then concat(employee,',',xxx) end as employees from CTE
where manager is not null)
select manager,string_agg(employees,',') as Team from CTE2 group by 1
Shared dataset is older, please provide for this query
scroll down to the bottom of the page and you will see problem 12 dataset link there
sorry my bad, have updated the link now. plz check
Have updated the correct link now
Toughest and most confusing one so far.
Can you please provide sql server solution
Sir the above solution works only when we add new teams in the table, if there is another hierarchy it will not give results. I write solution of above query it might works for that problem too team(please correct me sir if I'm wrong)
MS Sql
--new hierarchy
insert into company values ('david',null);
insert into company values ('bill','david');
select * from company;
with root as(
select employee from company
where manager is null)
,
teams as(
select concat('team ',row_number() over ( partition by root.employee order by (select null)),' of_',root.employee) team
, c.employee, root.employee as manager
from company c
inner join
root
on
root.employee=c.manager)
,rec as(
select team,employee,manager from teams
union all
select rec.team, c.employee, c.manager
from rec
inner join company c
on
rec.employee=c.manager
)
select team, string_agg(employee, ',') members
from
(select team, employee from rec
union
select team,manager from rec) k
group by team
Order by team;
plz provide class 12 th dataset, the shared dataset is of previous class
have updated the link now. plz check
It looks difficult to understand Thoufiq.
plz provide ms sql server solution
This is scary for me
🤯🤯
Here is my solution
with cte as(
SELECT c1.*,
c2.manager as second_manager
FROM company c1
join company c2
on c1.manager = c2.employee), cte2 as (
select cte.*,
c.manager as third_manager from
cte join company c
on cte.second_manager = c.employee),cte3 as(
select STRING_AGG(employee,',') employees, manager, second_manager,third_manager
from cte2
group by manager, second_manager,third_manager)
,cte4 as(
select employees, manager, second_manager,coalesce(third_manager,'') third_manager from cte3
where cte3.employees not in (select manager from cte3))
select concat('Team ',row_number() over(order by manager)) Teams,
concat(third_manager,',',second_manager,',', manager,',',employees) as
members from cte4
MS SQL:
WITH
cte_teams
AS
(
SELECT mng.employee,
CONCAT('Team ', ROW_NUMBER() OVER (ORDER BY mng.employee)) AS teams
FROM company root
JOIN company mng ON root.employee = mng.manager
WHERE root.manager IS NULL
),
cte
AS
(
SELECT c.employee,
c.manager,
t.teams
FROM company c
JOIN cte_teams t ON c.employee = t.employee
UNION ALL
SELECT c.employee,
c.manager,
cte.teams
FROM company c
JOIN cte ON c.manager = cte.employee
)
SELECT teams,
(SELECT employee
FROM company
WHERE manager IS NULL) + ', ' + STRING_AGG(employee, ', ') AS members
FROM cte
GROUP BY teams
ORDER BY teams;
here is my solution
with recursive cte as
(select 'team'||row_number()over(order by c2.employee) as teams, c1.employee e1, c2.employee e2 from company c1
join company c2 on c1.employee = c2.manager
where c1.manager is null),
cte2 as
(select teams, e1, e2
from cte
union
select cte2.teams,cte2.e1, c3.employee e2
from cte2
join company c3 on cte2.e2 = c3.manager),
cte3 as
(select teams, e1,string_agg(e2,',') members from cte2
group by 1,2)
select teams, (e1||','||members) as team from cte3
order by 1
with cte as (
SELECT c1.employee as E1, c2.employee as E2,c3.employee as E3,c3.manager as M1
FROM company c1
cross join company c2 on c1.manager=c2.employee
cross join company c3 on c2.manager=c3.employee AND C1.EMPLOYEE NOT IN (select distinct MANAGER from company WHERE MANAGER IS NOT NULL)
),
CTE2 AS (select group_concat(E1) AS EE,E2,E3,M1
from cte
GROUP BY E2,E3,M1)
SELECT CONCAT("Team ",row_number() OVER()) as Teams
,CASE WHEN M1 IS NULL THEN
CONCAT(e3,",",e2,",",EE)
else concat(m1,",",e3,",",e2,",",EE)
end AS MEMBERS
FROM CTE2
ORDER BY LENGTH(MEMBERS) desC;
ms sql approach
with a as (
select c2.employee, c2.manager, 1 hier, ROW_NUMBER() over(order by c2.employee) team
from company c1 join company c2 on c2.manager=c1.employee
where c1.manager is null
union all
select company.employee, company.manager, hier+1 hier, a.team
from a join company on a.employee=company.manager
),
b as (
select employee, team, ROW_NUMBER() over(partition by team order by hier) rn
from (
select manager employee, team, 0 hier
from a
where hier = 1
union all
select employee, team, hier
from a
) t )
select concat('team',team) team, string_agg(employee, ',') WITHIN GROUP (ORDER BY rn) members
from b
group by team
Great approach! Liked how you added root node to all 3 teams in cte b.