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?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download