Ayuda para modificar query

[flaviovich]

Si usas SQL 2000 prueba asi:

Código:

SELECT medio,
    SUM(CASE WHEN promotor = 'gdelcarmen' THEN total ELSE 0 END) [gdelcarmen],
    SUM(CASE WHEN promotor = 'jfuentes' THEN total ELSE 0 END) [jfuentes],
    SUM(CASE WHEN promotor = 'jllaguno' THEN total ELSE 0 END) [jllaguno],
    SUM(CASE WHEN promotor = 'larias' THEN total ELSE 0 END) [larias],
    SUM(CASE WHEN promotor = 'melizaga' THEN total ELSE 0 END) [melizaga]
FROM (
    SELECT medio, promotor, COUNT (*) AS total 
    FROM (
        SELECT CASE WHEN a.userAsignado = '' THEN 'Sin promotor' ELSE ISNULL(a.userAsignado, 'Sin promotor') END AS promotor, 
        CASE c.strMedio 
            WHEN 'Llamada telefónica' THEN 'Bolsa general' 
            WHEN 'Contacto personal' THEN 'Contacto personal'  
            WHEN 'E-mail' THEN 'Bolsa general' 
            WHEN 'Fax' THEN 'Bolsa general' 
            WHEN 'Página' THEN 'Bolsa general' 
            WHEN 'Visita del interesado' THEN 'Bolsa general'
            WHEN 'Hoja paja por correo' THEN 'Bolsa general' 
            WHEN 'Expo Management 2004' THEN 'Bolsa general' 
            WHEN 'MEDE' THEN 'Bolsa general' 
            WHEN 'PERFECCIONAMIENTO' THEN 'Bolsa general'
            ELSE ISNULL(c.strMedio, 'Vacio') 
        END AS medio 
        FROM cdimx_controlsolicitudes a 
        LEFT JOIN cdimx_candidatos b ON a.idSolicitud = b.intSolicitud 
        LEFT JOIN cdimx_c_medios c ON b.intMedio = c.intId 
        WHERE idSolicitud >= 19000 
    ) AS a 
    GROUP BY promotor, medio
) T
ORDER BY medio