How to bring, in different columns, values from the same column of another table, with different conditions

Question

Hi, what I need is something like this:

SELECT  users.[UsuarioId]
      , users.[Ativo]
      , users.[TipoFuncaoUsuarioId]
      , users.[ConcessionariaId]
      , users.[DataAdesao]

     (SELECT contato.[Contato] Celular
        FROM [USUARIO_CONTATO] contato, [USUARIO] users
 WHERE users.[UsuarioId] = contato.[UsuarioId]    
 AND   contato.[TipoUsuarioContatoId] = 1) 

    (SELECT contato.[Contato] Residencial
    FROM [USUARIO_CONTATO] contato, [USUARIO] users
 WHERE users.[UsuarioId] = contato.[UsuarioId]    
 AND   contato.[TipoUsuarioContatoId] = 1) 

The goal is to bring the contacts of each participant, who today are in the contact table, to this new table of the query. The participant has 4 types of contact: cellular, residential, commercial and email. They all stay in the contact table, in the same column, and have different ids.

Thank you!!

Answer 1

The goal is to bring the contacts of each participant, who today are contact table for this new query table.

This query can be performed in several ways, I will demonstrate two ways, the first following the style of your original query and the second using the command CASE.

1st Form - Original Query Style - Using Sub-Consultations

SELECT  u.[UsuarioId]
      , u.[Ativo]
      , u.[TipoFuncaoUsuarioId]
      , u.[ConcessionariaId]
      , u.[DataAdesao]
      , (SELECT contato.[Contato] Celular
           FROM [USUARIO_CONTATO] contato
          WHERE contato.[UsuarioId]  = u.[UsuarioId] 
            AND contato.[TipoUsuarioContatoId] = 1)  AS Celular
      , (SELECT contato.[Contato] Celular
           FROM [USUARIO_CONTATO] contato
          WHERE contato.[UsuarioId]  = u.[UsuarioId]   
            AND contato.[TipoUsuarioContatoId] = 2)  AS Residencial
      , (SELECT contato.[Contato] TipoTres
           FROM [USUARIO_CONTATO] contato
          WHERE contato.[UsuarioId]  = u.[UsuarioId]   
            AND contato.[TipoUsuarioContatoId] = 3)  AS TipoTres         
      , (SELECT contato.[Contato] TipoQuatro
           FROM [USUARIO_CONTATO] contato
          WHERE contato.[UsuarioId]  = u.[UsuarioId]   
            AND contato.[TipoUsuarioContatoId] = 4)  AS TipoQuatro
 FROM users u   

Please note that the Sub-Consultation references u.[UsuarioId] of the main query, that is, for each user 4 sub-consultations will be made.

2nd Way: Using the command CASE:

 SELECT u.[UsuarioId]
      , u.[Ativo]
      , u.[TipoFuncaoUsuarioId]
      , u.[ConcessionariaId]
      , u.[DataAdesao]
      , CASE WHEN c.TipoUsuarioContatoId = 1 THEN c.Contato END AS Celular
      , CASE WHEN c.TipoUsuarioContatoId = 2 THEN c.Contato END AS Residencial 
      , CASE WHEN c.TipoUsuarioContatoId = 3 THEN c.Contato END AS TipoTres  
      , CASE WHEN c.TipoUsuarioContatoId = 4 THEN c.Contato END AS TipoQuatro  
   FROM users u, USUARIO_CONTATO c
   WHERE u.UsuarioId = c.UsuarioId

This solution is being held a single query in the two tables involved and relating them in the clause where.
I suggest you use this query using the command case for making the consultation more readable, concise and, I believe, performative. Adeque the consultation to your reality replacing TipoTres and TipoQuatro mentioned in the consultations according to their reality.