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

0 votes
1 answer
76 views
sql-server-2008 view substring
                          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
                        
Asked by zenith (3 rep)
Jul 9, 2020, 06:57 AM
Last activity: Jul 9, 2020, 09:58 AM
SQL Server 2008 - Replacing csv substrings in view-row using data from other table

Related Questions
