Here's a solution for all those who still think SQL Server 2016 is the latest and greatest version:

DECLARE @do TABLE (doid int, tranid varchar(100))
DECLARE @tblTransporter TABLE (tranID int, transporter varchar(100))

INSERT INTO @do
SELECT  doid, tranid
FROM    (
    VALUES  (1,'1 2 3')
    ,   (2,'2 4')
) t (DOID,TranID)

INSERT INTO @tbltransporter
SELECT  tranid,Transporter
FROM    (
    VALUES  (1,'ABC Tran')
    ,   (2,'BBC Tran')
    ,   (3,'CBC Tran')
    ,   (4,'DBC Tran')
) t (TranID,Transporter)

SELECT  doid
,   STUFF((SELECT   ISNULL(',' + t.transporter, '')
        FROM    (
            SELECT  cast('<root><i>' + replace(tranid, ' ', '</i><i>') + '</i></root>' AS xml) x
            ) x
        CROSS apply x.nodes('root/i') n(n)
        LEFT JOIN @tblTransporter t
            ON  t.tranID = n.value('.', 'INT')
        ORDER BY n.value('.', 'INT')
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 1, '')
FROM    @do

It looks a bit busy but what it does is create an xml from the list of tranIds, and then split it by using nodes, and then concatenates it back using another FOR XML PATH

You can try with STRING_AGG()

Select o.DoNo,STRING_AGG(t.Transporter,',') as tpt 
  From DO o
 outer apply String_Split(o.Transporter,' ') s
  left join Transporter as t on t.TID = s.value
 GROUP BY o.DoNo

This is an other solution using GROUP BY and STRING_AGG only :

select DOID, STRING_AGG(t.Transporter,',') as tpt
from TableDO d
inner join TblTransporter t on cast(d.TranID as varchar) like concat('%',t.TranID, '%')
group by DOID

Result :

DOID    tpt
1       ABC Tran,BBC Tran,CBC Tran
2       BBC Tran,DBC Tran

Demo here