0

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
  • Unnest Value (STRING_SPLIT function), join CodeValue, aggregate back (STRING_AGG function). – Akina Jul 9 '20 at 7:13
  • Replace table-type example data with their CREATE TABLE + INSERT INTO scripts (or make online fiddle). And specify precise server version. – Akina Jul 9 '20 at 7:14
  • Thanks for valuable input @Akina , however I forgot to mention that I'm stuck on SQL server 2008. Updated question in light of your comment – zenith Jul 9 '20 at 8:32
0

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).

  • This monstrosity works great! I don't believe the gap issue will be a problem, but thanks for the heads up. – zenith Jul 9 '20 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.