case when sql server in a single row


Hello, how are you? I wanted to know if you can help me with this. I have these 3 tables.

I need to bring the users and the profiles they have, as shown in the following photo.

but the result I get is the following:

The values that you show me are fine, but I want you to generate a single row per user.

I show you the Query of how I am doing it:

I thank you if you can help me. Thank you very much

asked by Jacqueline Stella 22.01.2018 в 14:16

1 answer


You simply need to use an aggregation function (for example, MAX ):

SELECT  u.IdUsuario,
        MAX(CASE WHEN pp.Id = 1 THEN 'Si' ELSE 'No' END) perfil1,
        MAX(CASE WHEN pp.Id = 2 THEN 'Si' ELSE 'No' END) perfil2,
        MAX(CASE WHEN pp.Id = 3 THEN 'Si' ELSE 'No' END) perfil3
FROM Usuario u
INNER JOIN PerfilUsuario pu
    ON u.idUsuario = pu.idUsuario
INNER JOIN Perfil pp
    ON pu.PerfilId = pp.Id
GROUP BY u.IdUsuario
answered by 22.01.2018 / 14:20