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
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.
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
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;
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;
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?
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.
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
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
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
with recursive cte as ( select two_level.employee_name, root.employee_name as manager_name, concat('Teams ',row_number()over(order by two_level.employee_name) )as teams, 1 as level from public.employee_manager root join public.employee_manager two_level on root.employee_name = two_level.manager_name where root.manager_name is null union select all_emp.*,cte.teams,cte.level+1 as level from public.employee_manager all_emp join cte on all_emp.manager_name = cte.employee_name ) select teams,string_agg(members,',') as members_list from ( select teams, case when level=1 then concat_ws(',',manager_name,employee_name) else concat_ws(',',employee_name) end as members from cte )a group by teams order by teams
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;
