user3172066 user3172066 - 4 months ago 10
SQL Question

Select All row where same name

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
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.

Comments