SQL Server 2008 - Replacing csv substrings in view-row using data from other table

Question

Using SQL server 2008, is there a way to perform SELECT query in a view replaces a row containing comma-separated values with their corresponding text value from another table? STRING_SPLIT and STRING_AGG is not available in 2008 version.

EDIT: Added create and insert script

CREATE TABLE Data(
Id int,
Value1 varchar(50) NULL,
Value2 int NULL,
Value3 datetime
)
GO

CREATE TABLE CodeValue(
    Id int,
    Code varchar(50) NULL
)
GO

INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (1, N'0;1;2', 43, CAST(N'2020-07-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (2, N'0;2;3', 652, CAST(N'2020-07-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (3, N'2', 1234, CAST(N'2020-07-02T00:00:00.000' AS DateTime))
GO

INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (0, N'Apple')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (1, N'Orange')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (2, N'Banana')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (3, N'Dogmeat')
GO

Consider my view contains data from two tables; Data and CodeValue, that would look like this:

Data
Id | Value | Value2 | Value 3
==============================
  1| 0;1;2| some other data
  2| 0;2;3|
  3| 2    |

CodeValue
Id | Code
=============
  0| Apple
  1| Orange
  2| Banana
  3| Dogmeat

So the actual output from the SELECT query in my view would be:

View
Id | Value 
============
  1| Apple, Orange, Banana
  2| Apple, Banana, Dogmeat
  3| Banana

I've messed around with stored procedures and functions, but can't wrap my head around those and how to actually implement this.

EDIT 2: Tried using STUFF() using the following template:

WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA
 ORDER BY FieldA;

However I can't seem to join codeValues on split values using homebrew split_string function:

CREATE FUNCTION dbo.tvf_SplitString (@stringToSplit VARCHAR(100))
RETURNS @returnList TABLE(Id VARCHAR(5))
AS 
BEGIN
    DECLARE @splitValue VARCHAR(5)
    DECLARE @post INT

    WHILE CHARINDEX(';', @stringToSplit) > 0
    BEGIN 
        SELECT @pos = CHARINDEX(';', @stringToSplit)
        SELECT @splitValue = SUBSTRING(@stringToSplit, 1, @pos-1)
        INSERT INTO @returnList
        SELECT @splitValue
        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit -@pos)
    END
    INSERT INTO @returnList
    SELECT @stringToSplit
    RETURN 
END

Answer 1

As a joke (but it works nevertheless):

WITH
cte1 AS ( SELECT id, ';'+value1+';' value1, value2, value3 
          FROM data ),
cte2 AS ( SELECT id, ';'+CAST(id AS VARCHAR)+';' sid, ';'+code+';' code
          FROM codevalue ),
cte3 AS ( SELECT cte1.id, REPLACE(cte1.value1, cte2.sid, cte2.code) value1, cte1.value2, cte1.value3, cte2.id cid
          FROM cte1 
          JOIN cte2 ON cte2.id = 0
        UNION ALL
          SELECT cte3.id, REPLACE(cte3.value1, cte2.sid, cte2.code) value1, cte3.value2, cte3.value3, cte2.id
          FROM cte3
          JOIN cte2 ON cte2.id = cte3.cid + 1 )
SELECT id, SUBSTRING(value1, 2, LEN(value1) - 2) value1, value2, value3 
FROM cte3
WHERE cid = ( SELECT MAX(id)
              FROM codevalue )
ORDER BY id

fiddle

Needs CodeValue.id have no gaps. If not then add ROW_NUMBER() column to cte2 and use it for next codevalue row selection (do not forget to alter starting value in static part from 0 to 1).