Cross Tab en SQL Server 2000

Estás en Inicio / Editorial / Bases de Datos

14.02.2006

Cross Tab en SQL Server 2000

Por Matías Thayer M. | 32,341 Lecturas
Archivado en: Bases de Datos

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

Califica esta nota:

(5 votos, promedio: 4.6 de 5)

Loading ...

Sobre el autor

Matías Thayer M.
Encargado del sitio de Webmagic, uno más de los miles de sitios para webmasters ;)

Comentarios

33 comentarios en total.

GILOF 09.03.2006 - 16:01 - #

Exelente ejemplo de referencia cruzada me sirvio muchisimo. Gracias a quien lo publico.
No Registrado 05.05.2006 - 10:40 - #

Funciona de maravilla…… gracias…..

Una pregunta…… como puedo pasarle parametros de un formulario para que me muestre solo datos que el usuario el pida y que no tome toda los datos de la BD’s?
Danie 10.07.2006 - 11:39 - #

Excelente, te pasaste de verdad….graciassssssssssssss
LYNA 27.07.2006 - 23:05 - #

EXCELENTE MUCHAS PERO MUCHAS GRACIAS Y SE LE AGRADECE POR LA AYUDA BRINDADA
eduardoalcon 10.08.2006 - 17:33 - #

Me gustaria saber como puedo realizar ese dichoso PIVOT con una tabla que contiene lo siguiente:

id_senal fecha_registro Valor_Alarma
a01u1001 23/01/2005 01:23:09 p.m. 0
a01u1002 23/01/2005 01:23:09 p.m. 1
a01u1003 23/01/2005 01:23:09 p.m. 0
a01u1004 23/01/2005 01:23:09 p.m. 0
a01u1001 23/01/2005 01:24:09 p.m. 1
a01u1002 23/01/2005 01:24:09 p.m. 0
a01u1003 23/01/2005 01:24:09 p.m. 1
a01u1004 23/01/2005 01:24:09 p.m. 1
a01u1001 23/01/2005 01:25:09 p.m. 1
a01u1002 23/01/2005 01:25:09 p.m. 0
a01u1003 23/01/2005 01:25:09 p.m. 1
a01u1004 23/01/2005 01:25:09 p.m. 1

y asi consecutivamente…

Lo que quiero es que esta tabl[PHP][/PHP]a quede de la sigueinte forma:

Fecha_Registro a01u1001 a01u1002 a01u1003 a01u1004
23/01/2005 01:23:09 p.m. 0 1 0 0
23/01/2005 01:24:09 p.m. 1 0 1 1
23/01/2005 01:25:09 p.m. 1 0 1 1

y asi consecutivamente segun los valores, para esto mis datos de entrada son los sigueintes:

la fecha de incio y fecha de termino

si alguien me puede auxiliar se lo agradeceria demasiado.

De antemano mil gracias.
Alexander Villegas Majano 11.09.2006 - 17:48 - #

Esta excelente ese ejemplo me sirvio para realizar un proceso muy pero muy complejo… gracias a quien lo escribio..
christian cordova 18.09.2006 - 18:42 - #

sql server esta muy interesante yo te lo recomiendo estudien en la san perdro del valle de mala
christian cordova 18.09.2006 - 18:45 - #

sql server esta muy interesante yo te lo recomiendo estudien en la san perdro del valle de mala
Mickel 18.09.2006 - 18:56 - #

igualmente sirve para 2005
Eduardo 17.10.2006 - 10:10 - #

Hola compañeros del diseño y manejo de BD…

Alguno de ustedes puede comentarme, por que SQL Server 2000 no puede autoincrementar su tamaño de data y log, segun va incrementando la BD…

El ejemplo que pusieron sobre Pivote es un beneficio muy grande para todos aquellos que programamos ya que nos ayudan mucho en el tiempo de ejecucion y representacion de la informacion… en mi caso para realizar reporte via Web…

Bueno espero que alguien me pueda explicar esto… que tengo de duda.

Saludos.
Edgar Morales 03.11.2006 - 11:39 - #

Muy buen ejemplo, necesito ayuda tengo una tabla de ventas diarias y quiero hacer una consulta para que las agrupe por horas, dias, semanas, y meses
George 09.11.2006 - 09:46 - #

Mis mas sinceras felicitaciones al autor de esta publicación es excelente, es de una gran ayuda y que bueno que existan personas que compartan su conocimiento
wil 15.11.2006 - 15:35 - #

Gracias, me ahorraste muchas horas
Kiko 19.11.2006 - 11:19 - #

Me da error
Servidor: mensaje 170, nivel 15, estado 1, línea 1
Línea 1: sintaxis incorrecta cerca de \’,\’.

Editando el PR muchas instrucciones aparecen en rojo por las comillas. ¿están correctas?
Alberto 22.11.2006 - 11:06 - #

Exelente ejemplo, Para la limitante de los 8000 caracteres puden declarar varias variables: varchar(8000) y armar la consulta en el execute.
ejem.
EXECUTE (@SQL+@SQL2+@SQL3)

gracias…..
Marco 05.01.2007 - 12:30 - #

Los invito a leer este ejemplo, que no tiene la limitación del tamaño de 8000 caracteres de la consulta, aunque es un poco más complejo.
Saludos
McLobo 06.01.2007 - 11:14 - #

Bonito ejemplo, gracias, es muy util
TuTo 16.01.2007 - 11:04 - #

Pues el ejemplo de Mathias Thayer no esta nada mal, pero la limitante me toco a mi, pues la variable es de 8000 y mis registros sobrepasaban los 16.000, en http://www.forosdelweb.com proponen un concatenado de consulta es decir EXEC =(@SQL+@SQL+@SQL) asi se distribuye el codigo, pues no recomiendo esta opcion, la desarrolle y es muy compleja, poco reutilizable,y consume muchos recursos del servidor, pues ya no mas de bla bla bla, os recomiendo 100% la Opcion de MARCO,resolvio mi problema y tiene la misma tendencia.

Charly ®
TuTo
PiPe R 18.01.2007 - 07:45 - #

No se pero no me resulta entendible el codigo de marco, creo que el ejemplo de mathias me resulta pero el problema es la variable que ejecuta el sql final es limitada, y actualmente ese es mi problema, alguien lo ha resuleto ? por favor ayudenme
TuTo 19.01.2007 - 09:20 - #

Finalmente, con la ayuda de Daniel00 logramos sacar el problema de la variable de 8000
es un poco recursivo, pero funciona, ya hay dos metodos de como hacerlo, uno el metodo de Marco, 2 el metodo de Mathias,

Aconsejo observar esta opcion para SQL SERVER 2005

http://www.elguille.info/NET/ADONET/firmas_salva_Pivot_Unpivot.htm
TuTo 19.01.2007 - 09:21 - #

Se me olvidaba , la solucion esta aqui
http://www.forosdelweb.com/showthread.php?t=457281

Saludos
MONICA 21.02.2007 - 14:32 - #

ME SIRVIO DE MARAVILLA
Daniel 12.03.2007 - 12:20 - #

yo tengo otra solucion haber si les sirve, si tengo una tabla asi:
Year Quarter Amount
—- ——- ——
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

y la quiero ver asi :

Year 1990 1991
————————————-
Q1 1.1 2.1
Q2 1.2 2.2
Q3 1.3 2.3
Q4 1.4 2.4
el query que me hizo esto es :

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY Year
GO
esa consulta me transforma mi tabla, como tambien la uso para reportes lo unico que hago es que creo una vista con ese resultado y a esa vista la filtro por como yo quiera, espero les sirva saludos desde MX
arnold 14.03.2007 - 16:31 - #

quiero hacer una consulta de mis tablas por fechas por ejemplo hago la sig consulta
SELECT *FROM SISMEMPLEADO WHERE FECHA=’ %2007% ‘ Y SI METRAE EL RESULTADO, PERO CUANDO LE PONGO MES DIA NO ME CUENTRA NADA. EN ESE CAMPO HAY DATOS COMO
2007-02-15 00:00:00.000

ALGUIN PODRIA AYUDARME??
juan 23.08.2007 - 12:38 - #

srs. buenas tardes. alguien no sabe alguna funcion en sql server 2000 de como sacar el maximo dia del mes
para realizar operaciones dentro de esos rangos.
se los agradeceria bastante…
gracias
Gerry 12.10.2007 - 17:07 - #

Hola Juan,

Hay varias opciones, te mando una que se puede usar en SPs:

DECLARE @start SMALLDATETIME, @end DATETIME — need precision of datetime here
SET @start = 0 + DATEDIFF(DAY, 0, GETDATE()) - (DAY(GETDATE())-1)
SET @end = DATEADD(MILLISECOND, -3, DATEADD(MONTH, 1, @start))
SELECT … WHERE dt >= @start AND dt
Santiago G. 06.11.2007 - 14:50 - #

Muy bueno y practico .
Gracias Matias
naye 21.12.2007 - 10:45 - #

hola me marca error
en esta parte
SET @SQL=@SQL + ’TODOS AS T FROM ’ + @TABLA

Línea 48: sintaxis incorrecta cerca de ‘’’.
naye 21.12.2007 - 10:47 - #

pueden ayudarme
naye 21.12.2007 - 11:18 - #

esta excelente !!!!!
Sara 16.04.2008 - 13:01 - #

Excelente!!! Solo le cambias los parámetros y ya tienes tu pivot. Felicitaciones a su creador, me ahorró bastante tiempo.
Sebastian Plaza 17.04.2008 - 12:43 - #

Una bestia..

Funciona excelente

Muchas gracias
Angy 21.05.2008 - 00:28 - #

hola buenos dias; muy bueno tu cross tab pero no me aparecen datos al momento de generar el reporte en access no se si se deba a que es un adp