CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pr0no
Pr0no

Reputation: 4099

Merge multiple rows into 1

Consider the following output:

ObjectNumber  Field1  Field2  Field3
------------------------------------
      1         1       0       0
      1         0       0       1
      2         0       0       0
      2         1       0       0
      2         0       1       1

I need to "merge" these rows into 1 row per ObjectNumber. Assume that

  1. all Fields have either 0 or 1 as value and
  2. per ObjectNumber there is only one 1 per field

The expected output is adding up all the fields:

ObjectNumber  Field1  Field2  Field3
------------------------------------
      1         1       0       1
      2         1       1       1

How can this be done?

Upvotes: 1

Views: 85

Answers (3)

radar
radar

Reputation: 13425

As it was required to add up the number, we might have to use sum

Select objectnumber,
       Sum(field1) as field1,
       Sum(field2) as field2,
       Sum(field3) as field3
From  product
Group by objectnumber

Upvotes: 1

wvdz
wvdz

Reputation: 16641

In this case both max() en sum() will work, but sum() seems more appropriate since you say that the output is 'adding up all the fields'.

SELECT ObjectNumber,
       sum(Field1) AS Field1,
       sum(Field2) AS Field2,
       sum(Field3) AS Field3
FROM youroutput t
GROUP BY ObjectNumber;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Just use an aggregation query with max():

select ObjectNumber, max(Field1) as Field1, max(Field2) as Field2, max(Field3) as Field3
from youroutput t
group by ObjectNumber;

Upvotes: 1

Related Questions