SQL Split, Join & Merge

TableDO

| DOID     | TranID |
| -------- | ------ |
| 1        | 1 2 3  |
| 2        | 2 4    |

TblTransporter

| TranID   |Transporter |
| -------- | --------   |
| 1        | ABC Tran   |
| 2        | BBC Tran   |
| 3        | CBC Tran   |
| 4        | DBC Tran   |

Result require From TableDO

| DOID     | Transporter               |
| -------- | --------                  |
| 1        | ABC Tran,BBC Tran,CBC Tran|
| 2        | BBC Tran,DBC Tran         |

I have tried

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

Which Shows Result

| DONO     | Tpt        |
| -------- | --------   |
| 1        | ABC Tran   |
| 1        | BBC Tran   |
| 1        | CBC Tran   |
| 2        | BBC Tran   |
| 2        | DBC Tran   |

I do not want to use String_Split Function as it requre Database Compatibility.

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

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