SQL SERVER - return different columns of an inner join on the same row

Question

SQL SERVER - return different columns of an inner join on the same row

Navigation

#1 by (2 votes)

0

I have the following scenario:

Users table

Table users access

In the usuariosacessos table, each row has the id of the user, the name of the feature, and a field stating whether or not you have access.

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

I tried the following:

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 feature)

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

Is there any way to do this?

sql sql-server

asked by anonymous 14.03.2018 / 00:29

1 answer

How to make a count of repeated arguments in R problems with query in hql

score 2 · Accepted Answer

In a simple case like this, of just two columns, you can use sub-selects, and create the columns manually. In more complex cases, I could make a pivoted 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);

Selecting 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

Result:

Iputitin SQLFiddle