I have table look like the following

Result would be: The sum of column 3 group by column 1 column 3 of 2 and column 3 of 4 (excluding the / from col 2) For example, 1st row will be 100 200 140 200 = 640. Result would be like the following:

How can I make or what would be the query in sql server to get such resultant one?

CodePudding user response：

Try something like this:

DECLARE @data table ( Col1 int, Col2 nvarchar(3), Col3 int );
INSERT INTO @data VALUES
    ( 1, '2/4', 100 ),
    ( 1, '2/4', 200 ),
    ( 2, '1/3', 140 ),
    ( 3, '2/3', 120 ),
    ( 4, '3/4', 200 );

;WITH cte AS (
    SELECT
        Col1, Col2, SUM ( Col3 ) AS Col3Sum
    FROM @data AS d
    GROUP BY
        Col1, Col2
)
SELECT
    Col1, ( Col3Sum   Col2MatchesSum ) AS [Sum]
FROM cte
OUTER APPLY (

    -- get the sum of rows matching Col2 delimited values.
    SELECT SUM ( Col3 ) AS Col2MatchesSum FROM @data WHERE Col1 IN (
        SELECT CAST ( [value] AS int ) FROM STRING_SPLIT ( cte.Col2, '/' )
    )

) AS n
ORDER BY
    Col1;

RETURNS

 ------ ----- 
| Col1 | Sum |
 ------ ----- 
|    1 | 640 |
|    2 | 560 |
|    3 | 380 |
|    4 | 520 |
 ------ -----