Merging two rows into one in the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merging two rows into one in the same table

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2013-03-04 : 09:35:25

Let us say we have a table with the following fields and values

Field1, Field2, Field3, Field4
------------------------------
Name1, String1, 10 , Test1
Name1, String1, 20 , Test2

I want to merge those records into the same record so that the result
would be...

Field1, Field2, Field3, Field4
------------------------------
Name1, String1, 30 , Test1

The important part is to have a distinct Field2 and the sum of values of Field3 when Field1 and Field2 are the same...

Any advice please?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 10:01:55

What is the rule for Field4 that causes Test1 to be picked rather than Test2?

SELECT Field1, Field2, SUM(Field3) AS Field3,MIN(Field4) AS Field4
FROM TheTable
GROUP BY Field1, Field2;

mmalaka
Starting Member

33 Posts

Posted - 2013-03-04 : 10:03:54

There is no role...I do not care about it..but I need to select a value from both records...randomly or the top 1...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 23:04:41

quote:
Originally posted by mmalaka

There is no role...I do not care about it..but I need to select a value from both records...randomly or the top 1...

ok then James suggestion or below would do


SELECT  Field1, Field2,Field3, Field4
FROM
(
SELECT Field1, Field2, SUM(Field3) OVER PARTITION BY (Field1, Field2) AS Field3, Field4,ROW_NUMBER() OVER (PARTITION BY Field1, Field2 ORDER BY Field4) AS Seq
FROM Table
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources