Merge two row in one - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Merge two row in one

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2013-11-26 : 14:56:13

I have the following table:

Table 1

Year---Plant---TRUCK---Quantity
2013---ABC-----100-----4
2013---ABC-----101-----5
2013---XYZ-----100-----3
2013---XYZ-----101-----7

I need a select that will show:

Year---Plant---TRUCK---Quantity
2013---ABC-----100-----9
2013---XYZ-----100-----10

Of course I have more than 4 lines in my live table.

Any help is deeply appreciated.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-26 : 15:13:09

Here is something to look at:

SELECT Year, Plant, MIN(Truck) AS Truck, SUM(Quantity) AS Quantity
FROM Table1 
GROUP BY Year, Plant

djj

matrixrep
Starting Member

30 Posts

Posted - 2013-11-26 : 15:38:28

I change the data to represent what i am looking for:

Table 1

Year---Plant---TRUCK---Quantity
2013---ABC-----100-----4
2013---ABC-----101-----5
2013---ABC-----102-----5
2013---XYZ-----100-----5
2013---XYZ-----101-----3
2013---XYZ-----102-----7

I need a select that will show:

Year---Plant---TRUCK---Quantity
2013---ABC-----100-----9
2013---ABC-----101-----5
2013---XYZ-----100-----12
2013---XYZ-----101-----3

I retain the truck 100 and merge the quantity of truck 102 for each plant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:25:59

[code]
SELECT Year,
Plant,
CASE WHEN TRUCK = 102 THEN 100 ELSE TRUCK END AS TRUCK,
SUM(Quantity) AS Quantity
FROM Table
GROUP BY Year,
Plant,
CASE WHEN TRUCK = 102 THEN 100 ELSE TRUCK END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

matrixrep
Starting Member

30 Posts

Posted - 2013-11-27 : 09:21:41

Thank you visakh16

This is exactly what i was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 10:41:16

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources