-1

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user3812887
  • 439
  • 12
  • 33
  • clean your question, separate codes, align tables to look like a table. this way people will help you. – johnny Jul 07 '14 at 15:23

1 Answers1

0

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
Mike Deluca
  • 1,295
  • 2
  • 18
  • 41
  • I was trying to use this code with some modification (the unicode string to datatype conversion fails) – user3812887 Jul 07 '14 at 15:54
  • UPDATE dbo.Printers SET dbo.Printers.EmulationDescription = dbo.Emulations.[Description] FROM dbo.Printers LEFT OUTER JOIN dbo.Emulations ON dbo.Printers.EmulationId = dbo.Emulations.EmulationId – user3812887 Jul 07 '14 at 15:55
  • I am getting 2 syntax errors after @Description is declared: near = COALESCE , and another near ) towards the end; – user3812887 Jul 07 '14 at 19:49
  • I am getting 2 syntax errors after @Description is declared: near =COALESCE , and another near ) towards the end; ; can you kindly guide me with exact syntaxes ? It looks like there is something wrong near the subquery execution – user3812887 Jul 07 '14 at 19:58