WITH MIN_MAX AS (

  SELECT pct.cargoid AS PACKAGE_ID,
   MIN(ct.minfreight) AS MIN_minfreight,
   MAX(ct.maxfreight) AS MAX_maxfreight
  FROM packagecartype AS pct
  LEFT OUTER JOIN cartype...

I'm not aware of anything in the category of functionality that says "consolidate a given tag set".    Besides, there'd have to be a fair number of rules for such...

Just another way to do it:
CREATE TABLE #TEST (
    StopDateTime datetime,
    StartTime time
);
INSERT INTO #TEST (StopDateTime, StartTime)
SELECT
    StopDateTime = '2018-07-16 00:01:26.353',
    StartTime = '23:51';

SELECT

Have to agree with Scott on the use of an identifier (int value) for the table is a better choice, and you can always have a master table for the...