CODEWITHSUNDEEP
sqlrowsconcatenation
msinghm
msinghm

Reputation: 129

T-SQL - Concatenate Multiple Rows

I am struggling with a query to return a list of managers with their respective employees

I have three tables as follows: 

Managers
ManagerID   ManagerName
1           Bob
2           Sally
3           Peter
4           George

EmployeeManager
EmployeeID  ManagerID
1           1
1           1
2           2
2           2
3           3
3           3
4           4
4           4

Employees
EmployeeID  EmployeeName
1           David
1           Joseph
2           Adam
2           Pete
3           Mark
3           Mavis
4           Susan
4           Jennifer

Desired Result Set

ManagerName CountEmployee   Employees
Bob          2              David, Joseph
Sally        2              Anish, Pete
Peter        2              Mark, Mavis
George       2              Susan, Jennifer

The query I am currently using is as follows:

Select m.ManagerName 
        ,Count(e.EmployeeName) Over(Partition By m.ManagerID) as CountEmployee 
        ,Rank() Over(Partition By m.ManagerID Order By em.EmployeeID) [RankEmployee]
       ,e.EmployeeName
From dbo.Employees e
Left Join dbo.EmployeeManager em on em.ManagerID=e.ManagerID
Left Join dbo.Managers m on m.ManagerID=em.ManagerID;

This returns a list of managers and employees on individual rows but I'm struggling to concatenate the employee names as per the above table.

Any ideas or solutions?

Manpaal Singh

Upvotes: 0

Views: 216

Answers (3)

Sébastien
Sébastien

Reputation: 44

you can use recursive sql to convert rows in a string :

with t1 (mngId, empName) as (
    select a.mngId,
           b.empname
    from manager as a, employee as b
    where b.mngId = a.mngId),
t2 (mngID, nbr, empName, all_name) as (
    select mngId, 
        cast(1 as Int), 
        min(empName), 
        cast(min(empName) as varchar(1000)
    from t1 
    group by mngId
    union all
    select b.mngId,
        b.nbr+1,
        a.empName,
        trim(b.all_name) concat ', ' concat a.empName
    from t0 as a, t1 as b
    where b.mngId = a.mngId
        and a.empName > b.empName
        and a.empName = (
            select min( c.empName)
            from t0 as c
            where c.mngId = b.mngId
                and c.empName > b.empName )
)
select * 
from t1 as e
where nbr = ( 
    select max(nbr) 
    from t1 as d
    where d.mngId = e.mngId )

Upvotes: 0

Kumar_Vikas
Kumar_Vikas

Reputation: 845

You can stuff the result to comma seperated result.

 Select m.ManagerName 
                    ,Count(e.EmployeeName) Over(Partition By m.ManagerID) as CountEmployee 
                    ,Rank() Over(Partition By m.ManagerID Order By em.EmployeeID) [RankEmployee]
                   ,STUFF((SELECT  ',' + e.EmployeeName
                           FOR XML PATH('')), 1, 1, '') AS EmployeeName
        From dbo.Employees e
        Left Join dbo.EmployeeManager em on em.ManagerID=e.ManagerID
        Left Join dbo.Managers m on m.ManagerID=em.ManagerID

Upvotes: 1

st3_121
st3_121

Reputation: 32

SELECT M.ManagerName, E.EmployeeName 
FROM Managers AS M
INNER JOIN EmployeeManager AS EM ON M.ManagerID = EM.ManagerID
INNER JOIN Employees AS E ON EM.EmployeeID = E.EmployeeID
ORDER BY M.ManagerName

This will give a list of managers and their employees. when you fix the ID's of the employees table.

1       David
1       Joseph
2       Adam
2       Pete
3       Mark
3       Mavis
4       Susan
4       Jennifer

should be:

1       David
2       Joseph
3       Adam
4       Pete
5       Mark
6       Mavis
7       Susan
8       Jennifer

Upvotes: 0

Related Questions