Cross Tab en SQL Server 2000

Publicado el 14 de Febrero, 2006

Procedimiento que realiza la tarea, de convertir las filas en columnas (Pivot Table). De modo que las filas de una tabla se convierten en columnas, conocido también como CROSS TAB

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 la siguiente manera:

Select * From Negocios:

Cod_Negocio	Zona	Ventas_diarias	Supervisor
120	Centro	50000	DANIEL
122	Centro	50000	ESCULAPIO
123	Norte	10000	JUAN
125	Norte	30000	JUAN
444	Centro	15000	ESCULAPIO
545	Centro	50000	ESCULAPIO
231	Centro	100000	ESCULAPIO
121	Centro	30000	DANIEL
745	Sur	10000	DANIEL
522	Norte	850000	ESCULAPIO
111	Sur	10000	DANIEL
150	Austral	13000	JAIME

De este modo:

EXEC PR_crosstab ‘Negocios’, ‘Zona’, ‘Supervisor’, ‘ventas_diarias’, ‘AVG’

El * indica que se haga el cálculo por el total de registros.

Avg_Austral	Avg_Centro	Avg_Norte	Avg_Sur	Supervisor
NULL	40000.0	NULL	10000.0	DANIEL
NULL	53750.0	850000.0	NULL	ESCULAPIO
13000.0	NULL	NULL	NULL	JAIME
NULL	NULL	20000.0	NULL	JUAN

También podemos totalizar con “*” :

EXEC PR_crosstab ‘Negocios’, ‘Zona’, ‘*’, ‘ventas_diarias’, ‘COUNT’ //Ahora usamos Count

Count_Austral	Count_Centro	Count_Norte	Count_Sur	T
1	6	3	2	Todos

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 en esta parte se localiza 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 por si a caso

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 dinámico 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