0

I have the following Tables:


    CREATE TABLE #table1
    (Batchnumber INT NOT NULL,
     BOXID       TINYINT NOT NULL,
     ErrorID     TINYINT NULL,
     Weight      INT);

    CREATE TABLE #table2
    (ErrorID     TINYINT,
     Description VARCHAR(100));

    INSERT INTO #table1(Batchnumber, BOXID, ErrorID, Weight)
    VALUES (12345, 1, NULL, 1000),
    (12345, 2, 1, 1000),
    (12345, 3, 2, 1000);

    INSERT INTO #table2
    (ErrorID, Description)
    VALUES (1, 'Network Error'),
    (2, 'Keyboard to chair interface problem'),
    (3, 'Unknown Error');

I need to have the following in the temp table

TempTable
Batchnumber | Weight | Remarks
12345         3000     Network Error, Keyboard to chair interface problem

So he needs only 1 row with the Batchnumber, the sum of the weight for all 3 records and 1 field with all the error codes explained

Can someone help me with this please?

0
1

The FOR XML PATH solution for creating csv from rows tend to be the most common for 2016 and lower.

WITH cte
     AS (SELECT one.Batchnumber,
                one.Weight,
                two.Description
         FROM #table1 AS one
         LEFT OUTER JOIN #table2 AS two
              ON one.ErrorID = two.ErrorID)

SELECT Batchnumber,  SUM(Weight),
            STUFF((SELECT ',' + inr.Description
         FROM cte AS inr
         WHERE outr.Batchnumber = inr.Batchnumber FOR XML PATH(''), TYPE
     ).value('.', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
FROM cte AS outr
GROUP BY Batchnumber;

In 2017 there is the STRING_AGG function which makes this easier.

WITH cte
     AS (SELECT one.Batchnumber,
                one.Weight,
                two.Description
         FROM #table1 AS one
         LEFT OUTER JOIN #table2 AS two
              ON one.ErrorID = two.ErrorID)

SELECT  Batchnumber,SUM(Weight),STRING_AGG(Description,',')
FROM    cte
group by Batchnumber; 
1
  • Thanks. That did the trick! – Pascal Sparla Mar 24 '20 at 12:55

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.