[charlyrosero]

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

[daniel00]

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.

[charlyrosero]

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

[daniel00]

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.

[charlyrosero]

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

[daniel00]

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!

[charlyrosero]

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

[daniel00]

Y cuál fue el mensaje de error?

[Andres95]

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

[charlyrosero]

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

[Andres95]

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

[charlyrosero]

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

[daniel00]

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

[charlyrosero]

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

[daniel00]

Que bien charly.

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

Saludos.

[charlyrosero]

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 @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 !!

[daniel00]

A quien corresponda.

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

Saludos.

[lexmaniak]

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