Pivot rows to columns in SQL Server 2014

Question

I have 3 tables like image in below link:

https://drive.google.com/file/d/0B0P-s1mtoHkaY2hJUEJuTnZOYTQ/view

Can I display result as above one?

I researched on Google and find out that it is gotten by using pivot in SQL. But I can not got it. The helps will be appreciated.

Thanks in advance.

Answer 1

You can try joining the user and role tables together via the junction table, and then pivoting to get each role as a separate column:

SELECT
    u.Id AS UserID,
    u.UserName,
    MAX(CASE WHEN r.Name = 'Admin'    THEN 'TRUE' ELSE 'FALSE' END) AS Admin,
    MAX(CASE WHEN r.Name = 'Manager'  THEN 'TRUE' ELSE 'FALSE' END) AS Manager,
    MAX(CASE WHEN r.Name = 'Employer' THEN 'TRUE' ELSE 'FALSE' END) AS Employer
FROM Users u
LEFT JOIN UserRole ur
    ON u.Id = ur.UserID
LEFT JOIN Role r
    ON ur.RoleID = r.Id
GROUP BY
    u.ID,
    u.UserName
ORDER BY
    u.Id

Output:

Demo here:

Rextester