I have table like
name M1 m2 Tot
a 20 30 50
b 10 20 30
a 20 10 30
a 30 15 45
name Tot1 Tot2 Tot3
a 50 30 45
b 30 0 0
DECLARE @T TABLE (name VARCHAR(1), M1 INT, m2 INT, Tot INT)
DECLARE @MAXOBS INT
INSERT INTO @T VALUES
('a', 20, 30, 50),
('b', 10, 20, 30),
('a', 20, 10, 30),
('a', 30, 15, 45)
SELECT NAME,TOT1,TOT2,TOT3, TOT1+TOT2+TOT3 AS TOTAL
FROM
(
SELECT NAME,
ISNULL([1],0) AS TOT1,
ISNULL([2],0) AS TOT2,
ISNULL([3],0) AS TOT3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) RN,
NAME,
TOT
FROM T
) S
PIVOT (MAX(TOT) FOR RN IN ([1],[2],[3])) AS PVT
) T
If you think there will be more than 3 rows per name you will need dynamic sql.