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