SQL SERVER - return different columns from an Inner Join on the same row

Question

I have the following scenario:

Table users

Table users

On the table usuariosacessos, each line has the id user, the name of the functionality, and a field stating whether or not it has access.

In my specific case, I need to bring only two features.

I tried it this way:

select u.codus, u.nome, ua.acessarForm from usuarios as u 
inner join usuariosacessos as ua on u.codus = ua.codUsu
where u.login ='SS' and u.senha = '10' and ua.funcionalidade in ('frmTablet_Add', 'frmTablet_Remove');

but it returns me two lines (one for each functionality)

I would like to bring this data in just one line.

Is there any way to do that?

Answer 1

In a simple case like this, of only two columns, you can use sub-selects, and create the columns manually. In more complex cases, it could make a pivoting table.

Considering your current model this way:

create table usuarios 
( 
  id integer,
  nome varchar(50) );

create table usuariosacessos
(
  usuario integer,
  funcionalidade varchar(20),
  valor bit
);

insert into usuarios values (1,'usuario teste');

insert into usuariosacessos values (1,'frmTablet_Add',1);
insert into usuariosacessos values (1,'frmTablet_Remove',0);

select with sub-selects could solve your problem:

select
    u.id,
    u.nome,
    (select 
         a.valor 
     from usuariosacessos a 
     where a.usuario = u.id 
     and a.funcionalidade = 'frmTablet_Add' ) as frmTablet_Add,
    (select 
         a.valor 
     from usuariosacessos a 
     where a.usuario = u.id 
     and a.funcionalidade = 'frmTablet_Remove' ) as frmTablet_Remove
from usuarios u

Upshot:

I put in the Sqlfiddle