By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
4 rows affected
Data | B | C |
---|---|---|
G | 4 | 11 |
S | 3 | 4 |
T | 4 | 7 |
Warning: Null value is eliminated by an aggregate or other SET operation.
Data | B | C |
---|---|---|
G | 4 | 11 |
S | 3 | 4 |
T | 4 | 7 |
SELECT LEFT(M.[Data],1) AS [Data],
SUM(CASE V.Col WHEN N'B' THEN V.ColVal END) AS [B],
SUM(CASE V.Col WHEN N'C' THEN V.ColVal END) AS [C]
FROM dbo.Matrix M
CROSS APPLY(VALUES(N'B',M.[B1]),
(N'B',M.[B2]),
(N'C',M.[C1]),
(N'C',M.[C2]),
(N'C',M.[C3]))V(Col,ColVal)
GROUP BY LEFT(M.[Data],1)
ORDER BY LEFT(M.[Data],1);
Warning: Null value is eliminated by an aggregate or other SET operation.