Ndtyjky

I have the following data in SQL Server:

Data creation:

-- First we create some test data.

CREATE TABLE E

(

     [Epinum] VARCHAR(9), 

     [RTyp] VARCHAR(120), 

     [Date] DATETIME

);

GO



INSERT INTO E ([Epinum], [RTyp], [Date])

VALUES ('1', '', CONVERT(datetime, '2002-11-26 14:18:00', 20)),

       ('2', '', CONVERT(datetime, '2002-11-24 15:15:00', 20)),

       ('3', '', CONVERT(datetime, '2002-12-17 11:12:00', 20)),

       ('4', '', CONVERT(datetime, '2002-12-09 19:57:00', 20)),

       ('5', '', CONVERT(datetime, '2002-12-11 06:00:00', 20)),

       ('6', '', CONVERT(datetime, '2002-12-19 12:31:00', 20)),

       ('7', '', CONVERT(datetime, '2002-12-15 08:39:00', 20)),

       ('8', '', CONVERT(datetime, '2002-12-20 08:39:00', 20)),

       ('9', '', CONVERT(datetime, '2002-12-13 08:39:00', 20)),

       ('10', '', CONVERT(datetime, '2002-12-16 08:39:00', 20)),

       ('11', '', CONVERT(datetime, '2002-12-14 08:39:00', 20));

GO



CREATE TABLE UJ

(

    [Epinum] VARCHAR(9), 

    [RTyp] VARCHAR(3)

);

GO



INSERT INTO UJ ([Epinum], [RTyp])

VALUES ('1', '111'), ('1', '222'), ('1', '333'), ('1', '444'),

       ('2', '111'),

       ('3', '111'), ('3', '222'), ('3', '333'),

       ('4', '111'),

       ('5', '111'), ('5', '222'), ('5', '333'), ('5', '444'), 

       ('5', '555'), ('5', '666'), ('5', '777'), ('5', '888'),

       ('7', '111'),

       ('8', '111'),

       ('9', '111'), ('9', '222');

GO

T-SQL query:

-- Now build a query that will create the data we want. 

;WITH Tmp AS

(

    SELECT 

        *, 

        ROW_NUMBER() OVER (PARTITION BY Epinum ORDER BY Epinum) AS rownum

    FROM 

        UJ 

) 

SELECT DISTINCT 

    Epinum, 

    (SELECT 

         RTyp + CASE 

                   WHEN t.rownum = (SELECT MAX(rownum) FROM Tmp WHERE Epinum = s.Epinum) 

                      THEN '' 

                      ELSE '|' 

                END 

     FROM Tmp AS t

     WHERE t.Epinum = s.Epinum 

     FOR XML PATH(''), TYPE).value('(.)[1]', 'VARCHAR(MAX)') AS Piped 

FROM 

    Tmp AS s;

GO



-- Great. Now we update the E table, joining on Epinum. 

;WITH Tmp AS

(

    SELECT 

        *, ROW_NUMBER() OVER (PARTITION BY Epinum ORDER BY Epinum) AS rownum

    FROM

        UJ 

) 

UPDATE E

SET e.RTyp = q.Piped 

FROM 

    (SELECT DISTINCT 

         Epinum, 

         (SELECT RTyp + CASE 

                           WHEN t.rownum = (SELECT MAX(rownum) FROM Tmp WHERE Epinum = s.Epinum) 

                              THEN '' 

                              ELSE '|' 

                        END 

          FROM Tmp AS t

          WHERE t.Epinum = s.Epinum 

          FOR XML PATH(''), TYPE).value('(.)[1]', 'VARCHAR(MAX)') AS Piped 

      FROM 

          Tmp AS s) AS q 

INNER JOIN 

    E AS e ON q.Epinum = e.Epinum;

GO

This gets all of the RTyp columns for a particular Epinum and then pipe-delimits them and updates RTyp in table E. It works great, but the problem is, table E in production is ~2.3M rows and UJ is ~900k (both having lots more columns) and this query is taking far too long to perform the update.

How can I make this query more efficient?

UPDATE E 

SET e.RTyp = q.Piped 

FROM (

    SELECT Epinum, Piped = STUFF(

        (SELECT '|' + RTyp 

         FROM UJ 

         WHERE Epinum = t.Epinum 

         FOR XML PATH ('')), 1, 1, '') 

    FROM UJ AS t 

    GROUP BY Epinum) AS q INNER JOIN E AS e 

        ON q.Epinum = e.Epinum;

GO

If you are on the latest version of SQL Server you could try STRING_AGG

SELECT Epinum, STRING_AGG(Rtyp, '|')  delimited

FROM uj

GROUP BY Epinum;