WITH
SUBTYPES (CASE_TYPE, CASE_SUBTYPE, DESCRIPTION) AS
(SELECT BCBDCD, BCFZCD, BCB3TX FROM JEMSHDP11.CCBSTRP WHERE BCBDCD IN ('CV', 'SC', 'FD', 'EV')),
JP1_COMBINED (COURT, CASE_TYPE, CASE_SUBTYPE, TOTAL) AS
(
SELECT 'JP 1-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP11.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 1-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP12.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 2-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP21.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 2-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP22.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 5-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP51.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 5-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP52.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 6-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP61.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 6-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP62.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
)
SELECT CASE_TYPE, CASE_SUBTYPE, 
sum(CASE WHEN COURT='JP 1-1' THEN TOTAL ELSE 0 END) AS "JP 1-1", 
sum(CASE WHEN COURT='JP 1-2' THEN TOTAL ELSE 0 END) AS "JP 1-2",
sum(CASE WHEN COURT='JP 2-1' THEN TOTAL ELSE 0 END) AS "JP 2-1", 
sum(CASE WHEN COURT='JP 2-2' THEN TOTAL ELSE 0 END) AS "JP 2-2",
sum(CASE WHEN COURT='JP 5-1' THEN TOTAL ELSE 0 END) AS "JP 5-1", 
sum(CASE WHEN COURT='JP 5-2' THEN TOTAL ELSE 0 END) AS "JP 5-2",
sum(CASE WHEN COURT='JP 6-1' THEN TOTAL ELSE 0 END) AS "JP 6-1", 
sum(CASE WHEN COURT='JP 6-2' THEN TOTAL ELSE 0 END) AS "JP 6-2"
FROM JP1_COMBINED
GROUP BY CASE_TYPE, CASE_SUBTYPE