Thank you for anyone who is help. I am seeking help to create an sql script to do the following.

Have the following table

where the yield would be great like the following

I stumped at how to get 14928-001|002|003 as a merged value this is for T-SQL Thank you,

CodePudding user response：

If you were using SQL Server 2017 or later, you could use the STRING_AGG function. But since you are using SQL Server 2014, you have to use the FOR XML trick.

I have a solution, and it isn't elegant and there is most likely a better way to this, but here goes.

First, I set up a table variable and put your data in it. I then used a common table expression and some grouping to create an intermediate result:

Declare @Data TABLE(REF1 INT NOT NULL, REF2 INT NOT NULL, REF3 VARCHAR(20) NOT NULL, WT INT NOT NULL);
INSERT INTO @Data(REF1, REF2, REF3, WT) 
VALUES (91112, 43111, '14928-001', 2), 
(91112, 43111, '14928-002', 5), 
(91113, 42555, '14940-001', 3), 
(91112, 43111, '14928-003', 4), 
(91114, 42556, '14941-001', 1);


;WITH Step1 AS 
(
SELECT REF1, REF2, REF3, 
        LEFT(REF3, CHARINDEX('-', REF3) - 1) AS REF3_LEFT, 
        SUBSTRING(REF3, CHARINDEX('-', REF3)   1, LEN(REF3) - CHARINDEX('-', REF3)) AS REF3_RIGHT,
        WT
FROM @Data 
) 
SELECT p.REF1, p.REF2, p.REF3_LEFT,
    (SELECT '/'   REF3_RIGHT FROM Step1 AS sub 
        WHERE p.REF1 = sub.REF1 AND p.REF2 = sub.REF2 
        ORDER BY sub.REF3_RIGHT 
        FOR XML PATH (''), TYPE).value('.','VARCHAR(MAX)') AS suffixes, 
        SUM(WT) AS WT
FROM Step1 AS p
GROUP BY REF1, REF2, REF3_LEFT 
ORDER BY REF1, REF2;

The result of this look like the following:

This is very close... all we need to do now is replace the leading / of suffixes with a - and the concatenate it to REF3_LEFT and we will have it. So I will make another common table expression from the first and get the result you have shown above:

Declare @Data TABLE(REF1 INT NOT NULL, REF2 INT NOT NULL, REF3 VARCHAR(20) NOT NULL, WT INT NOT NULL);
INSERT INTO @Data(REF1, REF2, REF3, WT) 
VALUES (91112, 43111, '14928-001', 2), 
(91112, 43111, '14928-002', 5), 
(91113, 42555, '14940-001', 3), 
(91112, 43111, '14928-003', 4), 
(91114, 42556, '14941-001', 1);


;WITH Step1 AS 
(
SELECT REF1, REF2, REF3, 
        LEFT(REF3, CHARINDEX('-', REF3) - 1) AS REF3_LEFT, 
        SUBSTRING(REF3, CHARINDEX('-', REF3)   1, LEN(REF3) - CHARINDEX('-', REF3)) AS REF3_RIGHT,
        WT
FROM @Data 
), Step2 AS 
(
SELECT p.REF1, p.REF2, p.REF3_LEFT,
    (SELECT '/'   REF3_RIGHT FROM Step1 AS sub 
        WHERE p.REF1 = sub.REF1 AND p.REF2 = sub.REF2 
        ORDER BY sub.REF3_RIGHT 
        FOR XML PATH (''), TYPE).value('.','VARCHAR(MAX)') AS suffixes, 
        SUM(WT) AS WT
FROM Step1 AS p
GROUP BY REF1, REF2, REF3_LEFT 
) 
SELECT REF1, REF2, CONCAT(REF3_LEFT , '-', SUBSTRING(suffixes, 2, LEN(suffixes) -1)) AS REF3, WT 
FROM Step2;

And this is the result:

There is the desired output you have shown. Obviously you do not need the table variable since you have the table and data, but it should be easy for you to replace @Data with the actual table name.