Select All row where same name

Question

I have table like

name  M1    m2   Tot

a      20   30    50

b      10   20    30    

a      20   10    30

a      30   15    45

from this table .i want to get table as

name   Tot1  Tot2  Tot3

a      50     30    45

b      30     0      0

how to get like that? How to write sql query? or any other way?

Answer 1

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.

Select All row where same name

Sign up