The sql code I wrote outputs three lines instead of one due to nulls in the other fields, how can i-CodePudding

The code

I am guessing due to the nulls being present, the code pulls each row as a separate row from the source table, but the data is all about one trip id, how can I amend this so that i get all three rows condensed into one.

Thank you in advance

CodePudding user response：

Try this,

    SELECT
       TOUR_ROUTE_ID
    ,  SUM( [Country 1]) AS [Country 1]
    ,  SUM( [Country 2]) AS [Country 2]
    ,  SUM( [Country 3]) AS [Country 3] 
FROM 
(
SELECT TRC.TOUR_ROUTE_ID
,  CASE WHEN TRC.SEQUENCE = 1 THEN MAX(TRC.COUNTRY_ID) END AS [Country 1]
,  CASE WHEN TRC.SEQUENCE = 2 THEN MAX(TRC.COUNTRY_ID) END AS [Country 2]
,  CASE WHEN TRC.SEQUENCE = 3 THEN MAX(TRC.COUNTRY_ID) END AS [Country 3] 
FROM IMTMS_TOUR_ROUTE_COUNTRY as TRC 
WHERE TRC.TOUR_ROUTE_ID = 4985 
GROUP BY TRC.TOUR_ROUTE_ID, TRC.SEQUENCE 
)A
GROUP BY TOUR_ROUTE_ID;

CodePudding user response：

To answer your question, just use aggregation (sum) for the case statements in the select list. Then group it by route id