| Respuesta: mostrar ultimo valor de cada columna
GRACIAS FLAVIOVICH,por darme la idea, al fin pude dar solucion y quedo asi
--creas el temporal
SELECT *, row_number() over (partition BY ano,meta,mes ORDER BY ano,meta,mes) x
INTO #rm
FROM resumen_metas
--seleccionar el ultimo dato de varias columnas
SELECT top 1 T0.ano,T0.meta,T0.mes,T1.m1,T2.m2, T3.m3, T4.m4, T5.m5, T6.m6, T7.m7, T8.m8, T9.m9,T10.m10, T11.m11,T12.m12
FROM #rm T0
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m1 from #rm T1 WHERE m1 >= 0 GROUP BY ano,meta,mes,x,m1 order by x desc
) T1 ON T0.ano = T1.ano AND T0.meta = T1.meta AND T0.mes = T1.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m2 from #rm T2 WHERE m2 >= 0 GROUP BY ano,meta,mes,x,m2 order by x desc
) T2 ON T0.ano = T2.ano AND T0.meta = T2.meta AND T0.mes = T2.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m3 from #rm T3 WHERE m3 >= 0 GROUP BY ano,meta,mes,x,m3 order by x desc
) T3 ON T0.ano = T3.ano AND T0.meta = T3.meta AND T0.mes = T3.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m4 from #rm T4 WHERE m4 >= 0 GROUP BY ano,meta,mes,x,m4 order by x desc
) T4 ON T0.ano = T4.ano AND T0.meta = T4.meta AND T0.mes = T4.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m5 from #rm T5 WHERE m5 >= 0 GROUP BY ano,meta,mes,x,m5 order by x DESC
) T5 ON T0.ano = T5.ano AND T0.meta = T5.meta AND T0.mes = T5.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m6 from #rm T6 WHERE m6 >= 0 GROUP BY ano,meta,mes,x,m6 order by x DESC
) T6 ON T0.ano = T6.ano AND T0.meta = T6.meta AND T0.mes = T6.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m7 from #rm T7 WHERE m7 >= 0 GROUP BY ano,meta,mes,x,m7 order by x DESC
) T7 ON T0.ano = T7.ano AND T0.meta = T7.meta AND T0.mes = T7.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m8 from #rm T8 WHERE m8 >= 0 GROUP BY ano,meta,mes,x,m8 order by x DESC
) T8 ON T0.ano = T8.ano AND T0.meta = T8.meta AND T0.mes = T8.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m9 from #rm T9 WHERE m9 >= 0 GROUP BY ano,meta,mes,x,m9 order by x DESC
) T9 ON T0.ano = T9.ano AND T0.meta = T9.meta AND T0.mes = T9.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m10 from #rm T10 WHERE m10 >= 0 GROUP BY ano,meta,mes,x,m10 order by x DESC
) T10 ON T0.ano = T10.ano AND T0.meta = T10.meta AND T0.mes = T10.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m11 from #rm T11 WHERE m11 >= 0 GROUP BY ano,meta,mes,x,m11 order by x DESC
) T11 ON T0.ano = T11.ano AND T0.meta = T11.meta AND T0.mes = T11.mes
LEFT JOIN (
SELECT top 1 ano,meta,mes,x,m12 from #rm T12 WHERE m12 >= 0 GROUP BY ano,meta,mes,x,m12 order by x DESC
) T12 ON T0.ano = T12.ano AND T0.meta = T12.meta AND T0.mes = T12.mes |