U_U no pues no...pero me quedo con que lo pude resolver jejejeje
Código SQL:
Ver originalCREATE TABLE #temp(
id INT,
id_personal INT,
aportacion INT,
interes INT,
week INT
)
DELETE FROM #temp
INSERT INTO #temp VALUES (1,1,100,50,1)
INSERT INTO #temp VALUES (1,1,200,50,2)
INSERT INTO #temp VALUES (1,1,300,50,3)
INSERT INTO #temp VALUES (1,1,400,50,4)
INSERT INTO #temp VALUES (1,1,500,50,5)
INSERT INTO #temp VALUES (2,2,100,50,1)
INSERT INTO #temp VALUES (2,2,200,50,2)
INSERT INTO #temp VALUES (2,2,300,50,3)
INSERT INTO #temp VALUES (2,2,400,50,4)
INSERT INTO #temp VALUES (2,2,500,50,5)
INSERT INTO #temp VALUES (2,2,600,50,6)
INSERT INTO #temp VALUES (2,2,700,50,7)
DECLARE @SELECT Nvarchar(MAX)
DECLARE @JOIN Nvarchar(MAX)
DECLARE @x INT
DECLARE @cuantos INT
DECLARE @x_v VARCHAR(20)
DECLARE @ant VARCHAR(20)
DECLARE @sig VARCHAR(20)
DECLARE @query Nvarchar(MAX)
SELECT @cuantos=MAX(week) FROM #temp
SET @x=1
SET @SELECT='select * from (
select row_number() over (partition by t1.id,t1.id_personal order by t1.id,t1.id_personal) as rn,'
SET @JOIN=''
while @x<=@cuantos
BEGIN
SET @x_v=CONVERT(VARCHAR(20),@x)
SET @ant=CONVERT(VARCHAR(20),@x-1)
SET @sig=CONVERT(VARCHAR(20),@x+1)
IF @x=@cuantos
BEGIN
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'
END
ELSE
BEGIN
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 +','
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)
END
SET @x=@x+1
END
SET @query=@SELECT + ' ' + @JOIN + ' ) as tabla where rn=1'
EXEC sp_sqlexec @query
Por cierto el query final queda de esta forma:
Código SQL:
Ver originalSELECT * FROM (
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)
LEFT JOIN #temp AS t3 ON (t2.id=t3.id AND t2.id_personal=t3.id_personal AND t3.week=t2.week+1)
LEFT JOIN #temp AS t4 ON (t3.id=t4.id AND t3.id_personal=t4.id_personal AND t4.week=t3.week+1)
LEFT JOIN #temp AS t5 ON (t4.id=t5.id AND t4.id_personal=t5.id_personal AND t5.week=t4.week+1)
) AS tabla WHERE rn=1
saludos!
aunque estaria bien que en lugar de gracias incluyera algun deposito en paypal 
