SQL Server Select single row from multiple duplicate rows with value 1 SQL query

7cwmlq89 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(101)

I need only one row from multiple duplicate rows but the value should not missed in others fields

Current table:

Name      ABC   XYZ APT STA ZIP
--------------------------------
Computer    0   0   0   0   0
Computer    0   0   0   1   0
Computer    0   0   0   0   0
Computer    1   0   0   0   0
Computer    0   0   0   0   0

Result I want:

Name      ABC   XYZ APT STA ZIP
-------------------------------- 
Computer    1   0   0   1   0

sql-server

来源：https://stackoverflow.com/questions/77410591/select-single-row-from-multiple-duplicate-rows-with-value-1-sql-query

1条答案

按热度按时间

js5cn81o1#

As many already suggested, it is a trivial task.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, _Name VARCHAR(20), ABC INT, XYZ INT, APT INT, STA INT, ZIP INT);
INSERT @tbl (_Name, ABC, XYZ, APT, STA, ZIP) VALUES
('Computer', 0, 0, 0, 0, 0),
('Computer', 0, 0, 0, 1, 0),
('Computer', 0, 0, 0, 0, 0),
('Computer', 1, 0, 0, 0, 0),
('Computer', 0, 0, 0, 0, 0);
-- DDL and sample data population, end

SELECT _Name, MAX(ABC) ABC, MAX(XYZ) XYZ, MAX(APT) APT, MAX(STA) STA, MAX(ZIP) ZIP
FROM @tbl
GROUP BY _Name;

Output

_Name	ABC	XYZ	APT	STA	ZIP
Computer	1	0	0	1	0

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Select single row from multiple duplicate rows with value 1 SQL query

1条答案

相关问题

热门标签

最新问答