Writing data to a single row of multiple tables

I have a table with error numbers and a reference for storing errors. I need to write errors in one line, I got the following option, but it depends on the order of records in the table.

DECLARE @Errors TABLE (
    RowNumber           INT
    ,ErrorId            INT
    ,ErrorDescription   NVARCHAR(4000)
    ,FileId             INT
);

INSERT  @Errors (RowNumber
                ,ErrorId
                ,ErrorDescription
                ,FileId)
VALUES (1, 3, N'', 0)
    ,(2, 3, N'', 0)
    ,(2, 4, N'', 10)
    ,(2, 5, N'', 10)
    ,(1, 4, N'', 10)
    ,(1, 5, N'', 10)
    ,(3, 4, N'', 10)
    ,(3, 5, N'', 10);

DECLARE @DictErrors TABLE (
    ErrorId         INT
    ,[Description]  NVARCHAR(4000)
);


INSERT  @DictErrors (ErrorId
                    ,[Description])
VALUES (3, N'Not found')
    ,(4, N'Disconnect')
    ,(5, N'Exception');

Result of work:

DECLARE @Description NVARCHAR(4000) = N'';
DECLARE @PreviousRow INT = 0;

UPDATE      err
SET         @Description = CASE
                            WHEN    @PreviousRow = err.RowNumber
                                    THEN @Description + dvr.[Description] + '; '
                            ELSE dvr.[Description] + '; '
                        END
            ,err.ErrorDescription = @Description
            ,@PreviousRow = err.RowNumber
FROM        @Errors     AS err
INNER JOIN  @DictErrors AS dvr
    ON err.ErrorId = dvr.ErrorId;

I expect to see two entry added with FileId = 0 and one “Not found” error and three entries with File Id = 10, and two “Disconnect; Exception” errors.

Answer

You can try below using STUFF() function

with cte1 as
(
select a.*,b.description from Errors a inner join DictErrors b on a.errorid=b.errorid
)

UPDATE err SET @Description=names
from @Errors err join
(
select rownumber,names= STUFF((
    SELECT ', ' + Description FROM cte1 b 
WHERE a.rownumber = b.rownumber
FOR XML PATH('')), 1, 1, '')
from cte1 a
group by rownumber
)X on err.rownumber=X.rownumber

Advertisement

Answer