Ver Mensaje Individual
  #6 (permalink)  
Antiguo 19/02/2013, 10:26
Avatar de Libras
Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 4 meses
Puntos: 774
Respuesta: Consulta dinámica SQL

U_U no pues no...pero me quedo con que lo pude resolver jejejeje


Código SQL:
Ver original
  1. CREATE TABLE #temp(
  2. id INT,
  3. id_personal INT,
  4. aportacion INT,
  5. interes INT,
  6. week INT
  7. )
  8. DELETE FROM #temp
  9.  
  10. INSERT INTO #temp VALUES (1,1,100,50,1)
  11. INSERT INTO #temp VALUES (1,1,200,50,2)
  12. INSERT INTO #temp VALUES (1,1,300,50,3)
  13. INSERT INTO #temp VALUES (1,1,400,50,4)
  14. INSERT INTO #temp VALUES (1,1,500,50,5)
  15.  
  16. INSERT INTO #temp VALUES (2,2,100,50,1)
  17. INSERT INTO #temp VALUES (2,2,200,50,2)
  18. INSERT INTO #temp VALUES (2,2,300,50,3)
  19. INSERT INTO #temp VALUES (2,2,400,50,4)
  20. INSERT INTO #temp VALUES (2,2,500,50,5)
  21. INSERT INTO #temp VALUES (2,2,600,50,6)
  22. INSERT INTO #temp VALUES (2,2,700,50,7)
  23.  
  24.  
  25. DECLARE @SELECT Nvarchar(MAX)
  26. DECLARE @JOIN Nvarchar(MAX)
  27. DECLARE @x INT
  28. DECLARE @cuantos INT
  29. DECLARE @x_v VARCHAR(20)
  30. DECLARE @ant VARCHAR(20)
  31. DECLARE @sig VARCHAR(20)
  32. DECLARE @query Nvarchar(MAX)
  33.  
  34. SELECT @cuantos=MAX(week) FROM #temp
  35.  
  36. SET @x=1
  37. SET @SELECT='select * from (
  38. select row_number() over (partition by t1.id,t1.id_personal order by t1.id,t1.id_personal) as rn,'
  39. SET @JOIN=''
  40.  
  41.  
  42. while @x<=@cuantos
  43. BEGIN
  44. SET @x_v=CONVERT(VARCHAR(20),@x)
  45. SET @ant=CONVERT(VARCHAR(20),@x-1)
  46. SET @sig=CONVERT(VARCHAR(20),@x+1)
  47.  
  48.  
  49. IF @x=@cuantos
  50. BEGIN
  51.   SET @SELECT=@SELECT + 't' + @x_v + '.aportacion as aportacion' + @x_v + ',' + 't' + @x_v + '.interes as interes'+ @x_v + ',isnull(t' +@x_v + '.week,t' +@x_v + '.week+1) as week' + @x_v + ' from #temp as t1'
  52. END  
  53. ELSE
  54. BEGIN
  55. SET @SELECT=@SELECT + 't' + @x_v + '.aportacion as aportacion' + @x_v + ',' + 't' + @x_v + '.interes as interes'+ @x_v + ',isnull(t' +@x_v + '.week,t' +@x_v + '.week+1) as week' + @x_v +','
  56. SET @JOIN=@JOIN + 'left join #temp as t' + @sig + ' on (t' + @x_v + '.id=t' + @sig + '.id and t' + @x_v + '.id_personal=t' + @sig + '.id_personal and t' + @sig + '.week=t' + @x_v + '.week+1) ' + CHAR(13)
  57. END
  58. SET @x=@x+1
  59. END
  60. SET @query=@SELECT + ' ' + @JOIN + ' ) as tabla where rn=1'
  61.  
  62. EXEC sp_sqlexec @query

Por cierto el query final queda de esta forma:

Código SQL:
Ver original
  1. SELECT * FROM (
  2. SELECT ROW_NUMBER() OVER (partition BY t1.id,t1.id_personal ORDER BY t1.id,t1.id_personal) AS rn,t1.aportacion AS aportacion1,t1.interes AS interes1,isnull(t1.week,t1.week+1) AS week1,t2.aportacion AS aportacion2,t2.interes AS interes2,isnull(t2.week,t2.week+1) AS week2,t3.aportacion AS aportacion3,t3.interes AS interes3,isnull(t3.week,t3.week+1) AS week3,t4.aportacion AS aportacion4,t4.interes AS interes4,isnull(t4.week,t4.week+1) AS week4,t5.aportacion AS aportacion5,t5.interes AS interes5,isnull(t5.week,t5.week+1) AS week5 FROM #temp AS t1 LEFT JOIN #temp AS t2 ON (t1.id=t2.id AND t1.id_personal=t2.id_personal AND t2.week=t1.week+1)
  3. LEFT JOIN #temp AS t3 ON (t2.id=t3.id AND t2.id_personal=t3.id_personal AND t3.week=t2.week+1)
  4. LEFT JOIN #temp AS t4 ON (t3.id=t4.id AND t3.id_personal=t4.id_personal AND t4.week=t3.week+1)
  5. LEFT JOIN #temp AS t5 ON (t4.id=t5.id AND t4.id_personal=t5.id_personal AND t5.week=t4.week+1)
  6.  ) AS tabla WHERE rn=1

saludos!
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me