Using Case Statements As a Pivot – DB2 SQL

I want a list of motor carriers, with trailer equipment counts listed in separate columns. I am using case statements to categorize the different equipment types, but I’m getting multiple returns instead of seeing one return for each carrier.

I’ve tried ROW OVER () and get the same results.

SELECT C.ECP_CARRIER_IDENT_ID AS ECP_ID, C.CARRIER_NM, C.MC_NB, C.DOT_NB,
(CASE WHEN E.EQUIPMENT_TYPE_CD = 'TR' THEN E.EQUIPMENT_CT END) AS TRACTORS,
(CASE WHEN E.EQUIPMENT_TYPE_CD = 'SV' THEN E.EQUIPMENT_CT END) AS VAN_TRLS,
(CASE WHEN E.EQUIPMENT_TYPE_CD = 'FT' THEN E.EQUIPMENT_CT END) AS FLATBEDS
FROM ECP_CARRIER C
JOIN ECP_CARRIER_EQUIPMENT E ON C.ECP_CARRIER_IDENT_ID = E.ECP_CARRIER_ID
WHERE CERTIFIED_FG = 1
GROUP BY C.ECP_CARRIER_IDENT_ID, C.CARRIER_NM, C.MC_NB, C.DOT_NB,
E.EQUIPMENT_TYPE_CD, E.EQUIPMENT_CT

I would expect everything to be on one row, but this is the result that I’m getting

ECP_NB   CARRIER_NM     MC_NB    DOT_NB  TRACTORS VAN_TRLS   FLATBEDS
 1234   Bob's Trucking   606066     66    10               
 1234   Bob's Trucking   606066     66              12

Answer

Use aggregation functions and fix the group by:

SELECT C.ECP_CARRIER_IDENT_ID AS ECP_ID, C.CARRIER_NM, C.MC_NB, C.DOT_NB,
       MAX(CASE WHEN E.EQUIPMENT_TYPE_CD = 'TR' THEN E.EQUIPMENT_CT END) AS TRACTORS,
       MAX(CASE WHEN E.EQUIPMENT_TYPE_CD = 'SV' THEN E.EQUIPMENT_CT END) AS VAN_TRLS,
       MAX(CASE WHEN E.EQUIPMENT_TYPE_CD = 'FT' THEN E.EQUIPMENT_CT END) AS FLATBEDS
FROM ECP_CARRIER C JOIN
     ECP_CARRIER_EQUIPMENT E
     ON C.ECP_CARRIER_IDENT_ID = E.ECP_CARRIER_ID
WHERE CERTIFIED_FG = 1
GROUP BY C.ECP_CARRIER_IDENT_ID, C.CARRIER_NM, C.MC_NB, C.DOT_NB;

The combination of keys in the GROUP BY should define each row you want in the result set. You don’t want separate rows for E.EQUIPMENT_TYPE_CD (for instance) so it should not be in the GROUP BY.