SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 aggregate id field moving values to one row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrBloom
Starting Member

United Kingdom
32 Posts

Posted - 04/30/2013 :  11:51:13  Show Profile  Reply with Quote



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

United Kingdom
32 Posts

Posted - 04/30/2013 :  11:54:05  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/02/2013 :  02:03:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000