aggregate id field moving values to one row

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)

aggregate id field moving values to one row

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2013-04-30 : 11:51:13

ProductID date Weight Height Size
1 04/08/1994 3.2
1 04/08/1994 43.5
1 04/08/1994 33.5
2 07/07/2000 67.9
2 07/07/2000 67.8
2 07/07/2000 8.2

Hi I have a table like the one above where the blanks are null. How Do I group by productID and date so that weight, height and size values are placed in one row where productID and date are same. As per the result below. Can't think how to do it.
Thanks

ProductID date Weight Height Size
1 04/08/1994 3.2 33.5 43.5
2 07/07/2000 8.2 67.8 67.9

MrBloom
Starting Member

36 Posts

Posted - 2013-04-30 : 11:54:05

Sorry the table above didn't come out well. See below...

ProductID date Weight Height Size
1 04/08/1994 3.2 null null
1 04/08/1994 null null 43.5
1 04/08/1994 null 33.5 null
2 07/07/2000 null null 67.9
2 07/07/2000 null 67.8 null
2 07/07/2000 8.2 null null

Hi I have a table like the one above where the blanks are null. How Do I group by productID and date so that weight, height and size values are placed in one row where productID and date are same. As per the result below. Can't think how to do it.
Thanks

ProductID date Weight Height Size
1 04/08/1994 3.2 33.5 43.5
2 07/07/2000 8.2 67.8 67.9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 02:03:49

if you're sure that you'll always have single set of values for Weight,Height and Size for a ProductID,date group you can use this


SELECT ProductID,[date],
MAX(Weight) AS Weight,
MAX(Height) AS Height,
MAX(Size) AS Size
FROM Table
GROUP BY ProductID,[date]

------------------------------------------------------------------------------------------------------
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