I am trying to get a specific output using MSSQL from a table. The table data is as below. I want a new column that is a string, and to contain all the roles of one person. So in this case the result for Bob would be Role abc, Role def, Role ghi ; Tom - Role jkl In order to get this table below I am joining a few tables, I just can't work out how to output that string.

Thanks in advance!

What I have so far is just the simple select to get the data.

SELECT
    E.Name,
    E.ID.
    R.Role
FROM
    EMPLOYEE_ROLE ER
    JOIN ROLE R ON R.ID = ER.RoleID
    JOIN EMPLOYEE E ON ER.EmployeeID = E.ID

CodePudding user response：

You can use row_number() :

select id, name, 
       concat('Role ', row_number() over(partition by id order by name)) as Role
from t;

CodePudding user response：

A common method for something like this is to use FOR XML PATH ('') to achieve the concatenation, e.g.:

WITH cteEmployees AS
(
    SELECT
        E.Name
        , E.ID
        , R.[Role]
    FROM
        EMPLOYEE_ROLE ER
        JOIN [ROLE] R ON R.ID = ER.RoleID
        JOIN EMPLOYEE E ON ER.EmployeeID = E.ID
)

SELECT
    ID
    , Name
    , Roles
FROM
    (
        SELECT
            ID
            , Name
            ,
                STUFF(
                    (
                        SELECT ', '   [Role]
                        FROM cteEmployees e2
                        WHERE e2.Name = e1.Name
                        FOR XML PATH ('')
                    )
                , 1, 2, '') Roles
        FROM cteEmployees e1
    ) e
GROUP BY
    ID
    , Name
    , Roles

CodePudding user response：

In SQL Server 2017 and above, you would use STRING_AGG():

SELECT E.Name, E.ID,
       STRING_AGG(R.Role, ',') WITHIN GROUP (ORDER BY R.Role
FROM EMPLOYEE_ROLE ER JOIN
     ROLE R
     ON R.ID = ER.RoleID JOIN
     EMPLOYEE E
     ON ER.EmployeeID = E.ID
GROUP BY E.Name, E.ID;