Gracias.
Ejemplo de como me lo está mostrado
Citar
id1 | 5 | 10 |
id1 | | | 4 | 6
Ejemplo de como lo necesito
Citar
id1 | 5 | 10 | 4 | 6
Consulta SQL
Código:
SELECT CONCAT(agent.agent_name,' ',agent.agent_lastname) AS agent, `agent`.`npn`, SUBSTRING(`client`.`aplication_date`,1,4) AS pyear,GROUP_CONCAT(DISTINCT `client`.agent2 ORDER BY `client`.agent2 ASC SEPARATOR ' | ') AS agent2,
CASE WHEN `client`.`id_insured` = 1 THEN COUNt(`client`.id) END AS numclimol,
CASE WHEN `client`.`id_insured` = 1 THEN SUM(`client`.`family_menber`) END AS nummenmol,
CASE WHEN `client`.`id_insured` = 2 THEN COUNt(`client`.id) END AS numcliamb,
CASE WHEN `client`.`id_insured` = 2 THEN SUM(`client`.`family_menber`) END AS nummenamb,
CASE WHEN `client`.`id_insured` = 3 THEN COUNt(`client`.id) END AS numcliflo,
CASE WHEN `client`.`id_insured` = 3 THEN SUM(`client`.`family_menber`) END AS nummenflo,
CASE WHEN `client`.`id_insured` = 9 THEN COUNt(`client`.id) END AS numcliosc,
CASE WHEN `client`.`id_insured` = 9 THEN SUM(`client`.`family_menber`) END AS nummenosc,
CASE WHEN `client`.`id_insured` = 11 THEN COUNt(`client`.id) END AS numclicig,
CASE WHEN `client`.`id_insured` = 11 THEN SUM(`client`.`family_menber`) END AS nummencig,
CASE WHEN `client`.`id_insured` = 12 THEN COUNt(`client`.id) END AS numclibri,
CASE WHEN `client`.`id_insured` = 12 THEN SUM(`client`.`family_menber`) END AS nummenbri
FROM agent INNER JOIN `client` ON `client`.id_agent = agent.id WHERE `client`.`borrado` <> 1
AND SUBSTRING(`client`.`aplication_date`,1,4) = '2020' GROUP BY agent.id, `client`.`id_insured` ORDER BY agent.id ASc, `client`.`id_insured` ASC
CASE WHEN `client`.`id_insured` = 1 THEN COUNt(`client`.id) END AS numclimol,
CASE WHEN `client`.`id_insured` = 1 THEN SUM(`client`.`family_menber`) END AS nummenmol,
CASE WHEN `client`.`id_insured` = 2 THEN COUNt(`client`.id) END AS numcliamb,
CASE WHEN `client`.`id_insured` = 2 THEN SUM(`client`.`family_menber`) END AS nummenamb,
CASE WHEN `client`.`id_insured` = 3 THEN COUNt(`client`.id) END AS numcliflo,
CASE WHEN `client`.`id_insured` = 3 THEN SUM(`client`.`family_menber`) END AS nummenflo,
CASE WHEN `client`.`id_insured` = 9 THEN COUNt(`client`.id) END AS numcliosc,
CASE WHEN `client`.`id_insured` = 9 THEN SUM(`client`.`family_menber`) END AS nummenosc,
CASE WHEN `client`.`id_insured` = 11 THEN COUNt(`client`.id) END AS numclicig,
CASE WHEN `client`.`id_insured` = 11 THEN SUM(`client`.`family_menber`) END AS nummencig,
CASE WHEN `client`.`id_insured` = 12 THEN COUNt(`client`.id) END AS numclibri,
CASE WHEN `client`.`id_insured` = 12 THEN SUM(`client`.`family_menber`) END AS nummenbri
FROM agent INNER JOIN `client` ON `client`.id_agent = agent.id WHERE `client`.`borrado` <> 1
AND SUBSTRING(`client`.`aplication_date`,1,4) = '2020' GROUP BY agent.id, `client`.`id_insured` ORDER BY agent.id ASc, `client`.`id_insured` ASC