'Cross Tab' en SQL Server 2000
SQL
Publicado el 28 de Agosto del 2005 por Matías Thayer (2 códigos)
41.999 visualizaciones desde el 28 de Agosto del 2005
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.
"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
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
o bien podemos totalizar con "*":
EXEC PR_crosstab 'Negocios', 'Zona', '*', 'ventas_diarias', 'COUNT' //Ahora usamos Count
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!!).