Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

solucion a Cross table de Maestros del Web

Estas en el tema de solucion a Cross table de Maestros del Web en el foro de SQL Server en Foros del Web. Como van trabajo actualmente con SQL SERVER 2000, y necesito desarrollar un store Procedure que me pueda generar Consulta de referencias cruzadas, casualmente halle un ...
  #1 (permalink)  
Antiguo 17/01/2007, 11:36
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Exclamación solucion a Cross table de Maestros del Web

Como van

trabajo actualmente con SQL SERVER 2000, y necesito desarrollar un store Procedure que me pueda generar Consulta de referencias cruzadas, casualmente halle un codigo en Cross Table este codigo es muy bueno pero tiene la limitante de que la variable a ejecurar el sql es una varchar(8000) , en mi caso este es mi problema, pues necesito una variable de mas capacidad,

alguno de ustedes tiene idea de como programar este problema sin cambiar de plataforma ? es decir se que SQL SERVER 2005 trae la opcion de nvarchar(max) que es la capacidad suficiente para poder trabajar ?

compañeros muchas gracias por su colaboracion
  #2 (permalink)  
Antiguo 18/01/2007, 11:54
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

Que tal Charly.

Y yo que pense que esos 8000 caracteres eran suficientes para cualquier tipo de consulta cross tab.

Lo que puedes hacer es dos subconsultas, dejarlas en tablas temporal, y al final unir esas dos subconsultas.

Sólo una duda, de cuantas columnas te debe resultar tu cross-tab?.

Saludos.
  #3 (permalink)  
Antiguo 18/01/2007, 12:18
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Re: solucion a Cross table de Maestros del Web

daniel00

gracias por la ayuda, pues contabilizando los caracteres eran cerca de 15.000, creo que la limitante es la variable , aunque no se SQL SERVER 2000 cuantas columnas soporte, pero creo que eran cerca de 20 columnas pivots (o hasta mas),

mi objetivo es tomar datos de ventas, de un mes tomando como filas los datos del cliente, y como campos pivots la linea del producto, y como dato del pivot seria su cantidad,

pues he intentado hacerlo, pero el problema es que se como hacer las subconultas, me podrias colaborar daniel00 ?


muchas gracias por tu colaboracion
  #4 (permalink)  
Antiguo 18/01/2007, 12:30
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

Que tal Charly.

Pues 20 columnas no son muchas, yo he presentado hasta 72 sin problemas, quizá el problema se de a que los nombre de tus columnas son muy extensos.

Porque no nos pones la parte de tu código donde se incrementa más el tamaño de tu cadena.

Saludos.
  #5 (permalink)  
Antiguo 18/01/2007, 12:40
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Pregunta Re: solucion a Cross table de Maestros del Web

Daniel00

gracias de nuevo

pues utilizo el codigo de mathias thayer con algunas modificaciones

Código:
CREATE PROCEDURE Cross_table 
--Creamos el procedimiento almacenado.
@TABLA varchar(255),
@PIVOT  VARCHAR(255),
@AGRUPACION varchar(255),
@CAMPO varchar(255),
@CAMPO2  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 (6000))

SET @STRG='' 
SET @SQL=''

SET @[email protected]+ 'INSERT INTO #PIVOT SELECT DISTINCT ''' +','[email protected] + ' (CASE WHEN ' + @PIVOT +' = '''''' + RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''  THEN ' + @CAMPO + '  ELSE 0  END)  AS '+  ''''''' +   RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''''   FROM ' + @TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL  ' 
EXECUTE (@STRG) 

--otrra insercion de otro pivot en este caso los totales en $, es decir si genero 20 columnas se generan otras 20 columnas con el dato de $

SET @STRG= 'INSERT INTO #PIVOT SELECT DISTINCT ''' +','[email protected] + ' (CASE WHEN ' + @PIVOT +' = '''''' + RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''  THEN ' + @CAMPO2 + '  ELSE 0  END)  AS '+'''''$ ''+'''' +  RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''''   FROM ' + @TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL  ' 
EXECUTE (@STRG) 


SET @SQL ='SELECT ' + @AGRUPACION 
SELECT @SQL= @SQL + RTRIM(convert(varchar(500), PIVOT)) --> justo en esta consulta se llena la variable @SQL varchar(8000)
FROM #PIVOT ORDER BY PIVOT DESC 	
IF @AGRUPACION<>'*' 
BEGIN 
SET @[email protected]+' FROM ' + @TABLA + ' GROUP BY ' + @AGRUPACION +'  ORDER BY  '[email protected] 
END 
ELSE 
BEGIN 
SET @[email protected] + '''TODOS'' AS T FROM ' + @TABLA 
END   


GO
Graicas por la colaboracion
  #6 (permalink)  
Antiguo 18/01/2007, 13:04
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

Podrías poner por ejemplo una "corrida" de tu SP, incluyendo que nos muetres los parametros de entradas y de salida el mensaje de error que te genera, tal cual te sale, o la salida si es que no te genera error.

Saludos!
  #7 (permalink)  
Antiguo 18/01/2007, 13:16
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Exclamación Re: solucion a Cross table de Maestros del Web

daniel00

estos son unos de los resultados de la tabla #PIVOT

,SUM(CASE WHEN LN='maiz' THEN UN ELSE 0 END) AS 'maiz'
,SUM(CASE WHEN LN='banana' THEN UN ELSE 0 END) AS 'banana'
,SUM(CASE WHEN LN='bom bom' THEN UN ELSE 0 END) AS 'bom bom'
,SUM(CASE WHEN LN='ron kola' THEN UN ELSE 0 END) AS 'ron kola'
,SUM(CASE WHEN LN='papel higienico' THEN UN ELSE 0 END) AS 'papel higienico'
,SUM(CASE WHEN LN='algodones' THEN UN ELSE 0 END) AS 'algodones'
.
.
.
.
son 134 filas

y concatenadas en la variable @SQL seria algo asi

SELECT VENDEDOR,CLIENTE,SUCURSAL,DIRECCION,TELEFONO,BARRI O_V,SUBCATEGORIA,SUM(CASE WHEN LN='aceite' THEN UN ELSE 0 END) AS 'aceite',SUM(CASE WHEN LN='aceites' THEN UN ELSE 0 END) AS 'aceites'.......

ocupando la variable con 8000 caracteres
  #8 (permalink)  
Antiguo 18/01/2007, 13:19
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

Y cuál fue el mensaje de error?
  #9 (permalink)  
Antiguo 18/01/2007, 13:32
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 16 años, 9 meses
Puntos: 38
Re: solucion a Cross table de Maestros del Web

no puedes ir mas alla de los 8000 caracteres por cadena..
pero si puedes ejecutar una concatenacion de cadenas...

por ejemplo

Código:
declare @str1 varchar(100), @str2 varchar(100)

select @str1= 'print ''', @str2 = 'prueba 2 cadenas'''
Execute(@str1 + @str2)

Resultado :
prueba 2 cadenas
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #10 (permalink)  
Antiguo 18/01/2007, 15:15
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Pregunta Re: solucion a Cross table de Maestros del Web

daniel00

el error es el siguiente


Server: Msg 170, Level 15, State 1, Line 1

Línea 1: sintaxis incorrecta cerca de 's'.

esto se refiere a loque mathias dice,

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


la S del error fue el ultimo caracter, porque no se completo la consulta, es el error de que la variable queda corta.....

andres, no entiendo muy bien tu idea, podrias explicarte mejor ?

muchas gracias amigos por la ayuda
  #11 (permalink)  
Antiguo 18/01/2007, 15:48
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 16 años, 9 meses
Puntos: 38
Re: solucion a Cross table de Maestros del Web

que si detectas que llegaste al final de la cadena empieces a llenar la siguiente variable...
y al final hacer el execute sobre las cadenas concatenadas...

tendrias que tener control de cada concatenacion de codigo para evitar que te pases de los 8000 caracteres...
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #12 (permalink)  
Antiguo 18/01/2007, 15:58
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Exclamación Re: solucion a Cross table de Maestros del Web

Perfecto esa es la idea que tenia al principio, contar el numero de registros de la tabla pivot,hacer una division de el nuemro de registros sobre la cantidad a partir, y con el modulo de la division me daba cuenta si era par o impar , con ese dato pordria partricionar el resultado en 3 particiones si era impar o 4 si es par

despues de saber claramente cuantos eran los registros a partir, deberia hacer consultas de registros limitando la cantidad de registros es decir

si tengo 20 registros y quiero particionarlos en en 4 variables con 5 registros seria algo asi

var1= contrendra los registros de 1 - 5
var2= contrendra los registros de 6 - 10
var3= contrendra los registros de 11 - 15
var4= contrendra los registros de 16 - 20

de esta forma podria ejecutar perfectamente mi sql asi

exec(var1+var2+var3+var4)

incluso conozco la forma de hacer ese sql de particionamiento (funciona como el limit en Mysql),
el gran problema es que solo llegue hasta saber el modulo de la division, de ahi en adelante no pude, por ello busco ayuda en este foro, a que me ayuden con esta solucion en conjunto y poderla publicar a la comunidad

Última edición por charlyrosero; 18/01/2007 a las 16:07
  #13 (permalink)  
Antiguo 18/01/2007, 16:49
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

Que tal.

Siguiendo lo que nos dice Andres, podemos probar lo siguiente:

Para lo cual hay que cambiar las siguientes líneas:
Código:
...
--Le agregamos un campo idenity para identificar cada rgistro.
CREATE TABLE #PIVOT ( IdRow integer IDENTITY(1,1) , Pivote varCHAR (6000))
....

SET @[email protected]+ 'INSERT INTO #PIVOT (Pivote ) SELECT DISTINCT ''' +','[email protected] + ' (CASE WHEN ' + @PIVOT +' = '''''' + RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''  THEN ' + @CAMPO + '  ELSE 0  END)  AS '+  ''''''' +   RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''''   FROM ' + TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL  ORDER BY '  +  @PIVOT + ' DESC   '
EXECUTE (@STRG) 

--otrra insercion de otro pivot en este caso los totales en $, es decir si genero 20 columnas se generan otras 20 columnas con el dato de $

SET @STRG= 'INSERT INTO #PIVOT (Pivote , PivoteName)  SELECT DISTINCT ''' +','[email protected] + ' (CASE WHEN ' + @PIVOT +' = '''''' + RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''  THEN ' + @CAMPO2 + '  ELSE 0  END)  AS '+'''''$ ''+'''' +  RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''''   FROM  ' + TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL   ORDER BY '  +  @PIVOT + ' DESC   'EXECUTE (@STRG) 
.....
....

--Partimos la cadena SQL en dos secciones.
DECLARE @intTotRegs int
DECLARE @intnumRegs int
SELETECT @intTotRegs  = Count(*) FROM #PIVOTE
SET @intnumRegs = @intTotRegs  / 2

SET @SQL_1 ='SELECT ' + @AGRUPACION 

SET @SQL_2 = ''
SELECT @SQL_2= @SQL_2 + RTRIM(convert(varchar(500), PIVOT)) 
FROM #PIVOT 
WHERE IdRow <= @intnumRegs

SET @SQL_3 = ''
SELECT @SQL_3= @SQL_3 + RTRIM(convert(varchar(500), PIVOT)) 
FROM #PIVOT 
WHERE IdRow >  @intnumRegs

IF @AGRUPACION<>'*' 
BEGIN 
SET @[email protected]_4 +' FROM ' + @TABLA + ' GROUP BY ' + @AGRUPACION +'  ORDER BY  '[email protected] 
END 
ELSE 
BEGIN 
SET @[email protected]_4 + '''TODOS'' AS T FROM ' + @TABLA 
END
Ahora tenemos lo que dice Ándres:

Execute ( @SQL_1 + @SQL_2 [email protected]_3 @SQL_4 )

Esperemos que funcione.

Saludos y suerte!!
  #14 (permalink)  
Antiguo 19/01/2007, 09:15
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Re: solucion a Cross table de Maestros del Web

daniel00

En realidad no se como agradecerte este favor, funciona y muy bien, lo unico que no sabia era como almacenar el conjunto de resultados en una variable, de todos modos voy apublicar la solucion en maestrosdelweb.com


daniel Muchas gracias
  #15 (permalink)  
Antiguo 19/01/2007, 09:42
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

Que bien charly.

Pero también publicalá aquí, para que no nos quedemos en suspenso.

Saludos.
  #16 (permalink)  
Antiguo 19/01/2007, 09:52
Avatar de charlyrosero  
Fecha de Ingreso: julio-2006
Mensajes: 161
Antigüedad: 15 años, 2 meses
Puntos: 5
Re: solucion a Cross table de Maestros del Web

Ok , esta es la solucion al problemilla , cabe destacar que estre procedimiento hace 2 pivots.

esta probado en el Query Analizer, pero se puede convertir en Store
Procedure Con solo aumentar estas lineas

Código:
CREATE PROCEDURE Cross_table 
--Creamos el procedimiento almacenado.
@TABLA varchar(255),
@PIVOT  VARCHAR(255),
@AGRUPACION varchar(255),
@CAMPO varchar(255),
@CAMPO2  VARCHAR(255),
@CALCULO varchar(20)
AS

y se suprimirria las asignaciones en el siguiente codigo
Código:
DECLARE @TABLA varchar(255)
DECLARE @PIVOT  VARCHAR(255)
DECLARE @AGRUPACION varchar(255)
DECLARE @CAMPO varchar(255)
DECLARE @CAMPO2  VARCHAR(255)
DECLARE @CALCULO varchar(20)
DECLARE @STRG AS VARCHAR(8000) 
DECLARE @SQL1 AS  VARCHAR(200)
DECLARE @SQL2 AS  VARCHAR(8000)
DECLARE @SQL3 AS  VARCHAR(8000)
DECLARE @SQL4 AS  VARCHAR(2000)
DECLARE @CANT_REG AS INT
DECLARE @LIMIT AS INT


SET @TABLA='[Ventas Comerciales Regulares OPT]'
SET @PIVOT='LN'
SET @AGRUPACION ='[CODIGO VENDEDOR],VENDEDOR,NIT,CLIENTE,SUCURSAL,CANAL,[CLASE CLIENTE],DIRECCION,TELEFONO,BARRIO_V,CIUDAD,SUBCATEGORIA'
SET @CAMPO='UN'
SET @CAMPO2='T'
SET @CALCULO ='SUM'

CREATE TABLE #PIVOT (IdRow integer IDENTITY(1,1) , PIVOTE VARCHAR (6000))
-- limpiamos las variables por si a caso

SET @STRG='' 
SET @SQL1=''
SET @SQL2=''
SET @SQL3=''
SET @SQL4=''

---ejecutamos el primer PIVOT el cual suma cantidades

SET @[email protected]+ 'INSERT INTO #PIVOT (PIVOTE ) SELECT DISTINCT ''' +','[email protected] + ' (CASE WHEN ' + @PIVOT +' = '''''' + RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''  THEN ' + @CAMPO + '  ELSE 0  END)  AS '+  ''''''' +   RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''''   FROM ' + @TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL '
EXECUTE (@STRG) 

--ejecutamos el segunto PIVOT el cual suma Cantidades monetarias

SET @STRG= 'INSERT INTO #PIVOT (PIVOTE)  SELECT DISTINCT ''' +','[email protected] + ' (CASE WHEN ' + @PIVOT +' = '''''' + RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''  THEN ' + @CAMPO2 + '  ELSE 0  END)  AS '+'''''$ ''+'''' +  RTRIM(CAST(' + @PIVOT + ' AS VARCHAR(200))) +''''''''   FROM  ' + @TABLA + ' WHERE ' + @PIVOT + ' IS NOT NULL '
EXECUTE (@STRG) 



---HALLAMOS LA CANTIDAD TOTAL DE REGISTROS,  PARA DIVIDIRLOS POR 2 Y ASI CALCULAR EL SQL
SELECT  @CANT_REG= COUNT(*) FROM #PIVOT 

--HALLAMOS EL LIMITE DE REGISTROS
SET @LIMIT = @CANT_REG  /  2

--A CONTINUACION REALIZAMOS LAS CONSULTAS SQL CON LAS LIMITANTES

SET @SQL1= 'SELECT ' + @AGRUPACION 

SET @SQL2=''
SELECT @SQL2= @SQL2 + RTRIM(CONVERT(VARCHAR (500), PIVOTE)) 
FROM #PIVOT 
WHERE IdRow <= @LIMIT

SET @SQL3=''
SELECT @[email protected] +  RTRIM(CONVERT(VARCHAR (500), PIVOTE)) 
FROM #PIVOT 
WHERE IdRow > @LIMIT

SET @SQL4=''
SET @[email protected] + ' FROM ' + @TABLA + 'GROUP BY ' + @AGRUPACION + ' ORDER BY ' + @AGRUPACION

EXEC (@[email protected][email protected][email protected])


GO
se me olvidaba agradecer a Andres por su aporte con este problema !!

Gracias Daniel !!

Última edición por charlyrosero; 16/04/2007 a las 08:36
  #17 (permalink)  
Antiguo 19/01/2007, 10:37
 
Fecha de Ingreso: noviembre-2006
Ubicación: México
Mensajes: 866
Antigüedad: 14 años, 10 meses
Puntos: 8
Re: solucion a Cross table de Maestros del Web

A quien corresponda.

Creo que este post debería de estar en las FAQ's.

Saludos.
  #18 (permalink)  
Antiguo 18/11/2009, 13:01
 
Fecha de Ingreso: octubre-2009
Mensajes: 14
Antigüedad: 11 años, 11 meses
Puntos: 0
Respuesta: solucion a Cross table de Maestros del Web

Alguien sabe que debo agregar para que sume las columnas generadas?
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 03:58.