Foros del Web - solucion a Cross table de Maestros del Web

Foros del Web (http://www.forosdelweb.com/)

- SQL Server (http://www.forosdelweb.com/f87/)

- - solucion a Cross table de Maestros del Web (http://www.forosdelweb.com/f87/solucion-cross-table-maestros-del-web-457281/)

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

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.

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

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.

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 @STRG=@STRG+ 'INSERT INTO #PIVOT SELECT DISTINCT ''' +','+@CALCULO + ' (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 ''' +','+@CALCULO + ' (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 @SQL=@SQL+' FROM ' + @TABLA + ' GROUP BY ' + @AGRUPACION +'  ORDER BY  '+@AGRUPACION 

END 

ELSE 

BEGIN 

SET @SQL=@SQL + '''TODOS'' AS T FROM ' + @TABLA 

END   
 
 

GO

Graicas por la colaboracion

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!

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

Re: solucion a Cross table de Maestros del Web

Y cuál fue el mensaje de error?

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

:arriba:

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

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...

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

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 @STRG=@STRG+ 'INSERT INTO #PIVOT (Pivote ) SELECT DISTINCT ''' +','+@CALCULO + ' (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 ''' +','+@CALCULO + ' (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 @SQL_4=@SQL_4 +' FROM ' + @TABLA + ' GROUP BY ' + @AGRUPACION +'  ORDER BY  '+@AGRUPACION 

END 

ELSE 

BEGIN 

SET @SQL_4=@SQL_4 + '''TODOS'' AS T FROM ' + @TABLA 

END

Ahora tenemos lo que dice Ándres:

Execute ( @SQL_1 + @SQL_2 +@SQL_3 @SQL_4 )

Esperemos que funcione.

Saludos y suerte!!

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

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.

Re: solucion a Cross table de Maestros del Web

Ok , esta es la solucion al problemilla :policia: , 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 @STRG=@STRG+ 'INSERT INTO #PIVOT (PIVOTE ) SELECT DISTINCT ''' +','+@CALCULO + ' (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 ''' +','+@CALCULO + ' (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 @SQL3=@SQL3 +  RTRIM(CONVERT(VARCHAR (500), PIVOTE)) 

FROM #PIVOT 

WHERE IdRow > @LIMIT
 

SET @SQL4=''

SET @SQL4=@SQL4 + ' FROM ' + @TABLA + 'GROUP BY ' + @AGRUPACION + ' ORDER BY ' + @AGRUPACION
 

EXEC (@SQL1+@SQL2+@SQL3+@SQL4)
 
 

GO

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

Gracias Daniel !!

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.

Respuesta: solucion a Cross table de Maestros del Web

Alguien sabe que debo agregar para que sume las columnas generadas?