Realizar una tabla dinamica en SQL

ambichol · #1 (**permalink**) 19/08/2014, 14:41

Estimados,

Una consulta, se puede realizar una tabla dinamica de excel en SQL Server...??? Resulta que estoy tratando de realizar una consulta que me devuelva un formato similar a este:

01/08/2014
TELEFONO ENTRANTE SALIENTE
111111111 15 35
222222222 20 222
333333333 154 26
444444444 198 157
555555555 0 0

Sin embargo, tengo problemas para adaptarme a ese formato....alguna idea sobre si es posible realizar esto o no....??

iislas · #2 (**permalink**) 19/08/2014, 14:44

¿Cómo para que?

ambichol · #3 (**permalink**) 19/08/2014, 14:47

Pues para mantener la información almacenada de manera ordenada dentro de mi BD, en la que mostraria el telefono, el tipo de llamada realizada (entrada o salida) y la suma de todo el tiempo....

iislas · #4 (**permalink**) 19/08/2014, 14:52

Pues no se entiende, ¿Qué significa la fecha 01/08/2014?

ambichol · #5 (**permalink**) 19/08/2014, 14:55

ahh, pues eso es el dia en que se realizaron las llamadas, seria como decir:

telefono 01/08/2014 - saliente 01/08/2014 - entrante
1111111 152 158

espero se haya entendido.... :S

Libras · #6 (**permalink**) 19/08/2014, 14:55

mande????????

iislas · #7 (**permalink**) 19/08/2014, 14:58

jajajajajajaja, no pues esta bien tu explicación, ¿pero sabes acaso algo de MODELADO DE BASES DE DATOS?, deberías tener algo como:

FECHA TELEFONO ENTRANTE SALIENTE
01/08/2014 111111111 15 35
01/08/2014 222222222 20 222
01/08/2014 333333333 154 26
01/08/2014 444444444 198 157
01/08/2014555555555 0 0

ambichol · #8 (**permalink**) 19/08/2014, 15:07

Exacto iislas, similar a ese formato es el que tengo en mi BD, pero lo tengo de esta forma:

TELEFONO FECHA TIPO_LLAMADA DURACION
11111111 01/08/2014 ENTRANTE 154
11111111 01/08/2014 SALIENTE 250
22222222 01/08/2014 ENTRANTE 54
22222222 01/08/2014 SALIENTE 354

Pero queria consultar si es posible armar ese tipo de query, que es similar a una tabla dinamica de excel....

Libras · #9 (**permalink**) 19/08/2014, 15:22

si, eso se llama pivote, y se usa la funcion PIVOT, existen muchos temas en el foro acerca de pivot(aparte de documentacion en google)

iislas · #10 (**permalink**) 19/08/2014, 15:32

Código SQL:

Ver originalSELECT  TELEFONO,
SUM(CASE WHEN TIPO = 'ENTRANTE' THEN DURACION END) AS ENTRANTE,
SUM(CASE WHEN TIPO = 'SALIENTE' THEN DURACION END) AS SALIENTE
FROM TUTBLA
GROUP BY TELEFONO

¿Funciona?

ambichol · #11 (**permalink**) 19/08/2014, 16:06

Libras: el pivot ya lo use para armar las columnas de la siguiente manera:

TELEFONO FECHA_LLAMADA SALIENTE ENTRANTE

sin embargo, lo que deseo es que se muestre algo como esto:

TELEFONO (DIA 1 - MES 08)SALIENTE (DIA 1 - MES 08)ENTRANTE (DIA 2 - MES 08)SALIENTE (DIA 2 - MES 08)ENTRANTE ............(DIA N - MES 08)SALIENTE (DIA N - MES 08)ENTRANTE

iislas: el codigo si funciona, pero no como lo deseo hacerlo...que es similar a las cabeceras que indico arriba....

iislas · #12 (**permalink**) 19/08/2014, 16:17

Pues amigo, debes incrementar el numero de mes en el CASE, simple...........y claro, el año, por aquello de que tengas varios años.......

Libras · #13 (**permalink**) 19/08/2014, 16:23

Cita:

Iniciado por ambichol

Libras: el pivot ya lo use para armar las columnas de la siguiente manera:

TELEFONO FECHA_LLAMADA SALIENTE ENTRANTE

sin embargo, lo que deseo es que se muestre algo como esto:

TELEFONO (DIA 1 - MES 08)SALIENTE (DIA 1 - MES 08)ENTRANTE (DIA 2 - MES 08)SALIENTE (DIA 2 - MES 08)ENTRANTE ............(DIA N - MES 08)SALIENTE (DIA N - MES 08)ENTRANTE

iislas: el codigo si funciona, pero no como lo deseo hacerlo...que es similar a las cabeceras que indico arriba....

Un pivote dinamico, hay muchos por el foro ;) y usa poquito la cabeza(no para golpear el teclado jejejeje)

ambichol · #14 (**permalink**) 19/08/2014, 16:30

Efectivamente, el sum case funciono como queria, pero el query es muy largo:

Código SQL:

Ver originalSELECT TELEFONO,
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '19/06/2014' THEN SEGUNDOS END) AS [19_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '19/06/2014' THEN SEGUNDOS END) AS [19_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '20/06/2014' THEN SEGUNDOS END) AS [20_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '20/06/2014' THEN SEGUNDOS END) AS [20_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '21/06/2014' THEN SEGUNDOS END) AS [21_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '21/06/2014' THEN SEGUNDOS END) AS [21_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '22/06/2014' THEN SEGUNDOS END) AS [22_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '22/06/2014' THEN SEGUNDOS END) AS [22_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '23/06/2014' THEN SEGUNDOS END) AS [23_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '23/06/2014' THEN SEGUNDOS END) AS [23_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '24/06/2014' THEN SEGUNDOS END) AS [24_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '24/06/2014' THEN SEGUNDOS END) AS [24_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '25/06/2014' THEN SEGUNDOS END) AS [25_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '25/06/2014' THEN SEGUNDOS END) AS [25_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '26/06/2014' THEN SEGUNDOS END) AS [26_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '26/06/2014' THEN SEGUNDOS END) AS [26_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '27/06/2014' THEN SEGUNDOS END) AS [27_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '27/06/2014' THEN SEGUNDOS END) AS [27_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '28/06/2014' THEN SEGUNDOS END) AS [28_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '28/06/2014' THEN SEGUNDOS END) AS [28_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '29/06/2014' THEN SEGUNDOS END) AS [29_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '29/06/2014' THEN SEGUNDOS END) AS [29_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '30/06/2014' THEN SEGUNDOS END) AS [30_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '30/06/2014' THEN SEGUNDOS END) AS [30_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '31/06/2014' THEN SEGUNDOS END) AS [31_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '31/06/2014' THEN SEGUNDOS END) AS [31_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '01/07/2014' THEN SEGUNDOS END) AS [01_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '01/07/2014' THEN SEGUNDOS END) AS [01_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '02/07/2014' THEN SEGUNDOS END) AS [02_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '02/07/2014' THEN SEGUNDOS END) AS [02_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '03/07/2014' THEN SEGUNDOS END) AS [03_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '03/07/2014' THEN SEGUNDOS END) AS [03_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '04/07/2014' THEN SEGUNDOS END) AS [04_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '04/07/2014' THEN SEGUNDOS END) AS [04_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '05/07/2014' THEN SEGUNDOS END) AS [05_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '05/07/2014' THEN SEGUNDOS END) AS [05_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '06/07/2014' THEN SEGUNDOS END) AS [06_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '06/07/2014' THEN SEGUNDOS END) AS [06_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '07/07/2014' THEN SEGUNDOS END) AS [07_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '07/07/2014' THEN SEGUNDOS END) AS [07_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '08/07/2014' THEN SEGUNDOS END) AS [08_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '08/07/2014' THEN SEGUNDOS END) AS [08_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '09/07/2014' THEN SEGUNDOS END) AS [09_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '09/07/2014' THEN SEGUNDOS END) AS [09_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '10/07/2014' THEN SEGUNDOS END) AS [10_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '10/07/2014' THEN SEGUNDOS END) AS [10_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '11/07/2014' THEN SEGUNDOS END) AS [11_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '11/07/2014' THEN SEGUNDOS END) AS [11_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '12/07/2014' THEN SEGUNDOS END) AS [12_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '12/07/2014' THEN SEGUNDOS END) AS [12_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '13/07/2014' THEN SEGUNDOS END) AS [13_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '13/07/2014' THEN SEGUNDOS END) AS [13_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '14/07/2014' THEN SEGUNDOS END) AS [14_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '14/07/2014' THEN SEGUNDOS END) AS [14_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '15/07/2014' THEN SEGUNDOS END) AS [15_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '15/07/2014' THEN SEGUNDOS END) AS [15_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '16/07/2014' THEN SEGUNDOS END) AS [16_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '16/07/2014' THEN SEGUNDOS END) AS [16_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '17/07/2014' THEN SEGUNDOS END) AS [17_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '17/07/2014' THEN SEGUNDOS END) AS [17_ENTRANTE],
SUM(CASE WHEN TIPO_LLAMADA = 'SALIENTE' AND FECHA_LLAMADA = '18/07/2014' THEN SEGUNDOS END) AS [18_SALIENTE],
SUM(CASE WHEN TIPO_LLAMADA = 'ENTRANTE' AND FECHA_LLAMADA = '18/07/2014' THEN SEGUNDOS END) AS [18_ENTRANTE]
FROM #T1
GROUP BY TELEFONO
ORDER BY 1

Alguna manera mas corta de hacer esto...?

Libras: creeme que por estar pensando como hacer esto....estoy mas despeinado que otra cosa...jejeje...

Libras · #15 (**permalink**) 19/08/2014, 16:38

con un query dinamico y claro una tabla con tus fechas, el resultado sera el mismo pero no tendrias que escribir todo eso.......ahora la pregunta del millon, para que necesitas el reporte de esa manera??? y si eso te hace despeinar,ahora imaginate acomodar 45TB de informacion y crear sus respectivos filegroups y partition functions en una base de datos ;) hehehehehehe

ambichol · #16 (**permalink**) 19/08/2014, 16:42

mmm....se que con un query dinamico saldria eso....pero todo tiene relacion con lo que preguntas, necesito el reporte de esa manera porque el archivo fuente esta en ese formato, es decir desde el 19 del mes pasado hasta el 18 de este mes....debe estar ordenado por las fechas....es por eso que declaro el query asi....

Libras · #17 (**permalink**) 19/08/2014, 16:44

en lugar de que la aplicacion se adapte a la base de datos, la base de datos se adapta a la aplicacion, clasica logica de programador jejejejeje

ambichol · #18 (**permalink**) 19/08/2014, 16:51

jajajaja....en realidad....estoy armando mi propia BD con los archivos fuente que necesito para mi gestión diaria, y hago este proceso, debido a que para solicitar al área responsable de la elaboración de los reportes (Service Desk),la modificación, tomara mas tiempo del que actualmente cuento...por eso hago por mi lado esto....
Solo espero que a futuro no modifiquen la estructura de los reportes...jejejeje

Libras · #19 (**permalink**) 19/08/2014, 16:56

entonces te estas complicando demasiado se podria hacer una mejor estructura de la base pero si te sirve adelante......

ambichol · #20 (**permalink**) 19/08/2014, 17:09

si pues....eso estoy planteando en mi area....pero ya estara sujeto a criterio de los jefes....
Libras, una ultima consulta, acabo de hacer el query dinamico, sin embargo como puedo quitar la ultima coma al mostrar las columnas...?

Código SQL:

Ver originalDECLARE @QUERY VARCHAR(MAX)
DECLARE @INI INT
DECLARE @FIN INT
 
SET @INI = 1
SET @FIN = 31
 
WHILE @INI <= @FIN
BEGIN
SET @QUERY = 'SELECT TELEFONO,
ISNULL(SUM(CASE WHEN TIPO_LLAMADA = ''SALIENTE'' AND FECHA_LLAMADA LIKE '''+RIGHT('00'+CAST(@INI AS VARCHAR(2)),2)+'%'' THEN SEGUNDOS END),0) AS ['+RIGHT('00'+CAST(@INI AS VARCHAR(2)),2)+'_SALIENTE],
ISNULL(SUM(CASE WHEN TIPO_LLAMADA = ''ENTRANTE'' AND FECHA_LLAMADA LIKE '''+RIGHT('00'+CAST(@INI AS VARCHAR(2)),2)+'%'' THEN SEGUNDOS END),0) AS ['+RIGHT('00'+CAST(@INI AS VARCHAR(2)),2)+'_ENTRANTE],
FROM T1
GROUP BY TELEFONO
ORDER BY 1'
SET @INI = @INI + 1
PRINT (@QUERY)
 
END

iislas · #21 (**permalink**) 21/08/2014, 14:39

¿Y te funciona la comparación de las fechas?

FECHA_LLAMADA = '26/06/2014'