FiveTech Support Forums

WITH ent AS (
               SELECT 
                    carcasa,
                    codigo,
                    descripcion,
                    peso,
                    ROW_NUMBER() OVER (
                         PARTITION BY carcasa 
                         ORDER BY ffaena, carcasa, media, codigo
                    ) AS rn
               FROM EntdesoInforme
               )
               SELECT
               h.fechafaena,
               h.nroprod,
               h.productor,
               h.nrocon,
               h.consignatario,
               h.dicoseorigen,
               h.departamento,
               h.anio,
               h.ntropa,
               h.corrfaena,
               h.dotno,
               h.categoria,
               h.kgenpie,
               h.kgen3ra,
               h.kgen4ta,
               h.kdressing,
               (h.kdressing / NULLIF(h.kgenpie,0)) * 100 AS pdressing,
               h.kgmach,
               (h.kgmach / NULLIF(h.kgenpie,0)) * 100 AS pmach,
               h.udsmach,
               h.rend,
               h.ritual,
               h.hilton,
               h.uds2da,
               h.cotizacion, 
               h.valorfletepesos,
               h.valorfleteuds,
               (h.valorfleteuds / NULLIF(h.kgenpie,0)) AS udsfletexkg,
               h.fleteacargo,
               h.comision,
               (h.comision / NULLIF(h.kgenpie,0)) AS comisionxkg,
               h.ivacomision,
               (h.ivacomision / NULLIF(h.kgenpie,0)) AS ivacomisionxkg,
               -- pivot de entdeso
               MAX(CASE WHEN e.rn = 1 THEN e.codigo END) AS codigo1,
               MAX(CASE WHEN e.rn = 1 THEN e.descripcion END) AS desc1,
               MAX(CASE WHEN e.rn = 1 THEN e.peso END) AS peso1,
               MAX(CASE WHEN e.rn = 2 THEN e.codigo END) AS codigo2,
               MAX(CASE WHEN e.rn = 2 THEN e.descripcion END) AS desc2,
               MAX(CASE WHEN e.rn = 2 THEN e.peso END) AS peso2,
               MAX(CASE WHEN e.rn = 3 THEN e.codigo END) AS codigo3,
               MAX(CASE WHEN e.rn = 3 THEN e.descripcion END) AS desc3,
               MAX(CASE WHEN e.rn = 3 THEN e.peso END) AS peso3,
               MAX(CASE WHEN e.rn = 4 THEN e.codigo END) AS codigo4,
               MAX(CASE WHEN e.rn = 4 THEN e.descripcion END) AS desc4,
               MAX(CASE WHEN e.rn = 4 THEN e.peso END) AS peso4
               FROM HaciendaInforme h
               LEFT JOIN ent e 
                    ON e.carcasa = h.corrfaena
               GROUP BY
                    h.fechafaena,
                    h.nroprod,
                    h.productor,
                    h.nrocon,
                    h.consignatario,
                    h.dicoseorigen,
                    h.departamento,
                    h.anio,
                    h.ntropa,
                    h.corrfaena,
                    h.dotno,
                    h.categoria,
                    h.kgenpie,
                    h.kgen3ra,
                    h.kgen4ta,
                    h.kdressing,
                    h.kgmach,
                    h.udsmach,
                    h.rend,
                    h.ritual,
                    h.hilton,
                    h.uds2da,
                    h.cotizacion, 
                    h.valorfletepesos,
                    h.valorfleteuds,
                    h.fleteacargo,
                    h.comision,
                    h.ivacomision;