Código de SQL - 'Cross Tab' en SQL Server 2000

'Cross Tab' en SQL Server 2000gráfica de visualizaciones


SQL

estrellaestrellaestrellaestrellaestrella(9)
Publicado el 28 de Agosto del 2005 por Matías Thayer
37.581 visualizaciones desde el 28 de Agosto del 2005. Una media de 70 por semana
Este procedimiento almacenado permite "dar vuelta una tabla" de modo que las filas de una tabla se conviertan en columnas, lo que también se conoce como CROSS TAB o PIVOT TABLE. En access por ejemplo teníamos la instrucción TRANSFORM - PIVOT que nos permitía generar estas vistas sin mayores inconvenientes. En Sql Server 2000 esta funcionalidad no existe.

Versión 1
estrellaestrellaestrellaestrellaestrella(9)

Publicado el 28 de Agosto del 2005gráfica de visualizaciones de la versión: Versión 1
37.582 visualizaciones desde el 28 de Agosto del 2005. Una media de 70 por semana
estrellaestrellaestrellaestrellaestrella
estrellaestrellaestrellaestrella
estrellaestrellaestrella
estrellaestrella
estrella

"Cross Tab" en SQL Server 2000


Descripción:
Como me canse de hacer tantos cases, hice este pequeño procedimiento que realiza la tarea,de convertir las filas en columnas (Pivot Table)


Explicación: Este procedimiento almacenado permite "dar vuelta una tabla" de modo que las filas de una tabla se conviertan en columnas, lo que también se conoce como CROSS TAB o PIVOT TABLE. En access por ejemplo teníamos la instrucción TRANSFORM - PIVOT que nos permitía generar estas vistas sin mayores inconvenientes. En Sql Server 2000 esta funcionalidad no existe.


El procedimiento nos permitirá convertir
una tabla que se ve de esta manera:


Select * From Negocios




Cod_NegocioZonaVentas_diariasSupervisor
120Centro50000DANIEL
122Centro50000ESCULAPIO
123Norte10000JUAN
125Norte30000JUAN
444Centro15000ESCULAPIO
545Centro50000ESCULAPIO
231Centro100000ESCULAPIO
121Centro30000DANIEL
745Sur10000DANIEL
522Norte850000ESCULAPIO
111Sur10000DANIEL
150Austral13000JAIME

De esta manera:
EXEC PR_crosstab 'Negocios', 'Zona', 'Supervisor', 'ventas_diarias',
'AVG'
--El * indica que se haga el cálculo por el total de registros



40000.0
Avg_AustralAvg_CentroAvg_NorteAvg_SurSupervisor
NULLNULL10000.0DANIEL
NULL53750.0850000.0NULLESCULAPIO
13000.0NULLNULLNULLJAIME
NULLNULL20000.0NULLJUAN

o bien podemos totalizar con "*":
EXEC PR_crosstab 'Negocios', 'Zona', '*', 'ventas_diarias', 'COUNT' //Ahora usamos Count




Count_AustralCount_CentroCount_NorteCount_SurT
1632Todos

Los parámetros son los siguientes:


EXEC PR_crosstab TABLA, CAMPO PIVOT, CAMPO O CAMPOS AGRUPACION, CAMPO A CALCULAR, , TIPO DE CALCULO (AVG, COUNT, MAX, ETC)


Y aquí va el script del procedimiento:


-------------------------------------------------------------------------------------
--Creamos el procedimiento almacenado
CREATE PROCEDURE PR_crosstab
@TABLA varchar(255),
@PIVOT VARCHAR(255),
@AGRUPACION varchar(255),
@CAMPO varchar(255),
@CALCULO varchar(20)
AS

--Declaramos las variables que nos permitirán
crear el sql con los "CASES"

DECLARE @STRG AS VARCHAR(8000)
DECLARE @SQL AS VARCHAR(8000)
CREATE TABLE #PIVOT (
PIVOT VARCHAR (8000)
)
-- limpiamos las variables porsiacaso
SET @STRG=''
SET @SQL=''

-- ALMA MATTER DEL PIVOT TABLE
/* En el siguiente código realizamos un "select distinct" del campo que usaremos como pivote, a cada registro le concatenamos su correspondiente "CASE" y lo almacenamos en una tabla temporal llamada #PIVOT /*
SET @STRG=@STRG + 'INSERT INTO #PIVOT SELECT DISTINCT ''' + @CALCULO + '(CASE WHEN ' + @PIVOT + '=''''''+ RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(500)))
+ '''''' THEN ' + @CAMPO + ' ELSE NULL END) AS ''''' + @CALCULO + '_'' +
RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(500))) + '''''', '' AS PIVOT
FROM ' + @TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL'


EXECUTE (@STRG)


/*
--el sql dinamico de más arriba genera un script similar a éste,
-- (cambia según los parámetros que se ingresen

INSERT INTO #PIVOT
SELECT DISTINCT 'AVG(CASE WHEN campo_pivote=''' + RTRIM(CAST(campo_pivote AS VARCHAR(500)))
+ ''' THEN precio ELSE 0 END) AS ''' +
RTRIM(CAST(campo_pivote AS VARCHAR(500))) + ''',' AS PIVOT
FROM tu_tabla WHERE campo_pivote IS NOT NULL

--Con el cual se obtienen los valores de los registros que queremos que se conviertan en campos
--de nuestra nueva tabla
*/

/* A continuación generamos la consulta final, donde seleccionamos las columnas según la tabla #PIVOT y realizamos la agrupación correspondiente.
SET @SQL ='SELECT '
SELECT @SQL= @SQL + RTRIM(convert(varchar(500), pivot))
FROM #PIVOT ORDER BY PIVOT
IF @AGRUPACION<>'*'
BEGIN
SET @SQL=@SQL + @AGRUPACION + ' FROM ' + @TABLA + ' GROUP BY ' +
@AGRUPACION
END
ELSE
BEGIN
SET @SQL=@SQL + '''TODOS'' AS T FROM ' + @TABLA
END

/* Ejecutamos la consulta, si quieres ver como queda, cambia el EXECUTE(@SQL) por PRINT(@SQL) */
EXECUTE (@SQL)

/* OJO: Si la consulta resultante en @SQL tiene más de 8000 caracteres el script dará un error ya que el sql no quedará completo :(. */

-- FIN DE SP
------------------------------------------------------------------------------------------------------------


Bueno, espero que les sirva, les aviso que una de las limitantes es el tamaño de la variable @SQL, definida en VARCHAR (8000), que contendrá la sentencia final del script (una serie de CASES, tantos como registros distintos tenga la columna "PIVOT"). Si la consulta final sobrepasa los 8000 caracteres el SP se caerá :P. Espero solucionarlo en el futuro con la ayuda de la comunidá (shiaaaáa!!).

1
 



Comentarios sobre la versión: Versión 1 (9)

anonimo
06 de Junio del 2007
estrellaestrellaestrellaestrellaestrella
Excelente Codigo!! Hace exactamente lo que estaba buscando
Responder
Maykiway
27 de Julio del 2007
estrellaestrellaestrellaestrellaestrella
Excelente consulta, su aporte a sido de mi utilidad, muchas gracias.
Responder
Jorge W. Hernández Arias
26 de Octubre del 2007
estrellaestrellaestrellaestrellaestrella
Excelente, funciona muy bien. Feliciraciones.
Responder
Jorge W. Hernández Arias
26 de Octubre del 2007
estrellaestrellaestrellaestrellaestrella
Excelente, funciona muy bien. Muchas Gracias por su aporte. Felicitaciones.
Responder
will
13 de Agosto del 2008
estrellaestrellaestrellaestrellaestrella
exelente codigo el mejor que he visto que haga esto
podria la columna de agrupación quedar de primeras?
Responder
Edna
08 de Septiembre del 2010
estrellaestrellaestrellaestrellaestrella
Esta perfecto!!! Te envio mis mas sincero respeto por este procedimiento... me has ahorrado muchos dolores de cabeza....
Responder
Juan Carlos Ortiz
27 de Agosto del 2011
estrellaestrellaestrellaestrellaestrella
Agradezco el que te hayas tomado el tiempo en colocar esta aportación.
Me fué bastante útil Gracias!!!
Responder
guadalupe
09 de Septiembre del 2011
estrellaestrellaestrellaestrellaestrella
el codigo sirve par sql 2005 esque lo probe pero me marca error en el pivot podrias darme una idea ???


Mens 156, Nivel 15, Estado 1, Procedimiento crosstab3x1, Línea 13
Sintaxis incorrecta cerca de la palabra clave 'PIVOT'.
Mens 156, Nivel 15, Estado 1, Procedimiento crosstab3x1, Línea 23
Sintaxis incorrecta cerca de la palabra clave 'pivot'.
Responder
NAYSLA TORRES
23 de Noviembre del 2011
estrellaestrellaestrellaestrellaestrella
amigo gracias e buscado esto todo el dia y esta es la unica buena solucion que e encontrado .. lo entendi bn gracias
Responder

Comentar la versión: Versión 1

Nombre
Correo (no se visualiza en la web)
Valoración
Comentarios

http://lwp-l.com/s1217