Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » SQL » Consulta sobre case when contrucción de matriz

SQL - Consulta sobre case when contrucción de matriz

Volver
Nuevo Tema
<<>>
 
Vista:
sin imagen de perfil

Consulta sobre case when contrucción de matriz

Publicado por Elías (2 intervenciones) el 03/09/2015 19:24:11
Buenos días, amigos.

necesito construir una consulta que me permita extraer en sql server el listado de usuarios y los objetos a los que tiene acceso, de la siguiente manera:

En la primera columna tengo los nombres de usuario y su propietarios. En las columnas subsiguientes como título de columnas los objetos a los que el user tiene acceso. De tal forma que junto al nombre se marcan con X todos los objetos adjudicados al usuario.

Actualmente logro un registro por objeto, lo que no es nada estético. Yo desearía que si el usuario AB10203 tiene acceso a los objetos 1, 2 y 3 de los 1,2,3,4,5,...,n que hay, en una sola línea me aparezcan todas las x bajo el nombre de cada objeto.

Desde ya agradezco cualquier tip o aporte que puedan brindarme.

Esta es actualmente mi consulta

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select distinct
	a.USER,
	a.NAME AS PROPIETARIO,
	case when a.OBJECT='OBJETO 1' then 'X' else '' end as OBJETO 1,
	case when a.OBJECT='OBJETO 2' then 'X' else '' end as OBJETO 2,
                      ....
                      case when a.OBJECT='OBJETO n' then 'X' else '' end as OBJETO n
 
from
	(select distinct
			user, name,object
	 from
			BS_USERS_ACC
	 where
			OBJECT in ('OBJETO 1', 'OBJETO 2',..., 'OBJETO n' ) and ACC_STATUS='A') A
	group by A.USER, a.NAME,A.OBJECT order by A.USER asc
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 806
Bronce
Ha mantenido su posición en SQL (en relación al último mes)
Gráfica de SQL

Consulta sobre case when contrucción de matriz

Publicado por leonardo_josue (1173 intervenciones) el 03/09/2015 20:18:44
Hola Elías:

En primer lugar tendrías que decirnos, ¿con qué BD's estás trabajando? esto es muy importante, pues aunque la mayoría de los DBMS's se basan en un estándar de SQL, la verdad es que hay diferencias importantes entre cada uno de ellos. Ahora bien, dependiendo de tu respuesta, la consulta se puede simplificar o complicar... para el ejemplo voy a utilizar MySQL y trataré de usar sólo SQL Estandar, el cual podrías aplicar a cualquier Motor.

Si entendí bien, tienes una tabla más o menos así:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM tabla;
+-----------+----------+
| idUsuario | idObjeto |
+-----------+----------+
|         1 | objeto 1 |
|         1 | objeto 2 |
|         1 | objeto 3 |
|         2 | objeto 1 |
|         2 | objeto 3 |
|         3 | objeto 2 |
+-----------+----------+
6 rows in set (0.11 sec)

y lo que quieres hacer es una salida donde sólo exista un registro por usuario con N columnas, 1 en cada columna marcar con una X si el usuario tiene ese objeto asignado. Por lo que entiendo, tú haces algo como esto:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT
    ->   idUsuario,
    ->   CASE WHEN idObjeto = 'objeto 1' THEN 'X' ELSE '' END objeto_1,
    ->   CASE WHEN idObjeto = 'objeto 2' THEN 'X' ELSE '' END objeto_2,
    ->   CASE WHEN idObjeto = 'objeto 3' THEN 'x' ELSE '' END objeto_3
    -> FROM tabla;
+-----------+----------+----------+----------+
| idUsuario | objeto_1 | objeto_2 | objeto_3 |
+-----------+----------+----------+----------+
|         1 | X        |          |          |
|         1 |          | X        |          |
|         1 |          |          | x        |
|         2 | X        |          |          |
|         2 |          |          | x        |
|         3 |          | X        |          |
+-----------+----------+----------+----------+
6 rows in set (0.00 sec)

y lo que pretendes es agrupar los registros, esto mismo puedes hacerlo con una pequeña modificación, en primer lugar cambiar la asignación a X por uno, de tal manera que tu tabla te quede así:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT
    ->   idUsuario,
    ->   CASE WHEN idObjeto = 'objeto 1' THEN 1 ELSE 0 END objeto_1,
    ->   CASE WHEN idObjeto = 'objeto 2' THEN 1 ELSE 0 END objeto_2,
    ->   CASE WHEN idObjeto = 'objeto 3' THEN 1 ELSE 0 END objeto_3
    -> FROM tabla;
+-----------+----------+----------+----------+
| idUsuario | objeto_1 | objeto_2 | objeto_3 |
+-----------+----------+----------+----------+
|         1 |        1 |        0 |        0 |
|         1 |        0 |        1 |        0 |
|         1 |        0 |        0 |        1 |
|         2 |        1 |        0 |        0 |
|         2 |        0 |        0 |        1 |
|         3 |        0 |        1 |        0 |
+-----------+----------+----------+----------+
6 rows in set (0.00 sec)

Ahora, con esto, puedes utilizar la función de agrupación SUM para agrupar por columna, utilizando la consulta de arriba como una subconsulta, es decir, algo así:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT
    ->    idUsuario,
    ->    SUM(objeto_1) objeto_1,
    ->    SUM(objeto_2) objeto_2,
    ->    SUM(objeto_3) objeto_3
    -> FROM
    -> ( SELECT
    ->     idUsuario,
    ->     CASE WHEN idObjeto = 'objeto 1' THEN 1 ELSE 0 END objeto_1,
    ->     CASE WHEN idObjeto = 'objeto 2' THEN 1 ELSE 0 END objeto_2,
    ->     CASE WHEN idObjeto = 'objeto 3' THEN 1 ELSE 0 END objeto_3
    ->   FROM tabla
    -> ) T
    -> GROUP BY idUsuario;
+-----------+----------+----------+----------+
| idUsuario | objeto_1 | objeto_2 | objeto_3 |
+-----------+----------+----------+----------+
|         1 |        1 |        1 |        1 |
|         2 |        1 |        0 |        1 |
|         3 |        0 |        1 |        0 |
+-----------+----------+----------+----------+
3 rows in set (0.00 sec)

de esta manera, todos los registros quedan agrupados por el ID usuario. Si quieres que aparezca una X en lugar de un 1 lo único que tienes que hacer es formatear la salida.

1
2
3
4
5
6
7
+-----------+----------+----------+----------+
| idUsuario | objeto_1 | objeto_2 | objeto_3 |
+-----------+----------+----------+----------+
|         1 | X        | X        | X        |
|         2 | X        |          | X        |
|         3 |          | X        |          |
+-----------+----------+----------+----------+

Ojo, esto sirve solo dando por hecho que los objetos sólo aparecen una vez para cada usuario, si tienes duplicidad de registros, la respuesta ya no es válida.

Este no es el único camino para hacer la consulta, ni mucho menos el más óptimo ya que entre más objetos tengas, más condiciones tienes que agregar. Verifica si tu motor de BD's soporta la función PIVOT, que hace esto mismo pero de manera más facil.

Saludos
Leo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Consulta sobre case when contrucción de matriz

Publicado por Elías (2 intervenciones) el 04/09/2015 02:15:48
Hola, leonardo_josue. Muchas gracias por tu respuesta. Lo siento, di por sentado que al llamarse SQL el foro era SQL SERVER, que es el motor de bases de datos que utilizo. Te agradezco por tu respuesta. Está genial. Lo tomaré muy en cuenta.

Saludos,
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar