T-SQL, joining two tables between an integer column in the first table and a comma separated string column in the other

Question

I have a situation like this (T-SQL):

Table 1: dbo.Printers

EmulationID              EmulationDescription              PrinterID         Name

34,15,2                  NULL                              12                HP 1234 
15,2                     NULL                              13                IBM 321 
15                       NULL                              14                XYZ

Table 2: dbo.Emulations

EmulationID              Description

34                       HP
15                       IBM
2                        Dell

EmulationID column in dbo.Printers table is nvarchar/unicode string datatype, and integer datatype in the dbo.Emulations table.

Now I have to UPDATE the **EmulationDescription** column in the dbo.Printers table using a lookup on the dbo.Emulations table through the EmulationID column.

I need to get data like this in the dbo.Printers table:

EmulationID              EmulationDescription              PrinterID         Name

34,15,2                  HP,IBM,Dell                       12                HP 1234 
15,2                     IBM,Dell                          13                IBM 321 
15                       IBM                               14                XYZ

Can someone help me in detail, on how to get this issue resolved ?

I created the user-defined function dbo.ParseIdListToTable to convert string data in one row into multiple rows. However, I do not know to proceed further, on how to exactly join and then update.

Any suggestion will be greatly appreciated.

Answer 1

You could do something like this:

    CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
    RETURNS @TempTab TABLE
       (id int not null)
    AS
    BEGIN
        ;-- Ensure input ends with comma
        SET @InStr = REPLACE(@InStr + ',', ',,', ',')
        DECLARE @SP INT
    DECLARE @VALUE VARCHAR(1000)
    WHILE PATINDEX('%,%', @INSTR ) <> 0 
    BEGIN
       SELECT  @SP = PATINDEX('%,%',@INSTR)
       SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
       SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
       INSERT INTO @TempTab(id) VALUES (@VALUE)
    END
        RETURN
    END
    GO


DECLARE @Description VARCHAR(1000)

SELECT P.EmulationID,
    (SELECT  @Description = COALESCE(@Description + ',', '') + QUOTENAME(Description)
    FROM    dbo.Emulations
    WHERE EmulationID IN (SELECT * FROM dbo.CSVToTable(P.EmulationID))) AS 'Emulation Description,
P.PrinterID,
P.Name
FROM dbo.Printers P