Complex Query
-
I have a table called Inventory with the following schema ID, ItemID, Name, Value, CollectedDate All of the columns allow duplicates except ID, so there may be multiple instances of a given
Name, but with a differentCollectedDateFor a givenItemID, I need to return the Value with the latestCollectedDatefor each distinctName. To add to the complexity, I need to put all of the returned items into a single row, using theNameas the column name. So, what I'm starting with is this:ID ITEMID NAME VALUE COLLECTEDDATE
1 12 Test1 abc 01/01/2014 00:00:00
2 12 Test2 def 01/01/2014 00:00:00
3 12 Test3 ghi 01/01/2014 00:00:00
4 12 Test4 jkl 01/01/2014 00:00:00
5 12 Test1 mno 01/03/2014 00:00:00
6 12 Test3 stu 01/05/2014 00:00:00
7 12 Test4 vwx 01/06/2014 00:00:00
8 12 Test4 yz 01/09/2014 00:00:00
9 14 Test1 123 01/09/2014 00:00:00And my goal is to get to this:
ITEMID Test1 Test2 Test3 Test4
12 mno def stu yz
14 123 NULL NULL NULLI created the following temp tables: -
@tempNames- contains a list of all of the distinctNames -@tempIDsthat contains all of the uniqueItemIDs -@tempInventorythat contains all of the desired schema shown above. How do I get one row of info for eachItemIDto the@tempInventorytable? (I don't even know what to call what I'm trying to do. Otherwise, I'd google it).".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I have a table called Inventory with the following schema ID, ItemID, Name, Value, CollectedDate All of the columns allow duplicates except ID, so there may be multiple instances of a given
Name, but with a differentCollectedDateFor a givenItemID, I need to return the Value with the latestCollectedDatefor each distinctName. To add to the complexity, I need to put all of the returned items into a single row, using theNameas the column name. So, what I'm starting with is this:ID ITEMID NAME VALUE COLLECTEDDATE
1 12 Test1 abc 01/01/2014 00:00:00
2 12 Test2 def 01/01/2014 00:00:00
3 12 Test3 ghi 01/01/2014 00:00:00
4 12 Test4 jkl 01/01/2014 00:00:00
5 12 Test1 mno 01/03/2014 00:00:00
6 12 Test3 stu 01/05/2014 00:00:00
7 12 Test4 vwx 01/06/2014 00:00:00
8 12 Test4 yz 01/09/2014 00:00:00
9 14 Test1 123 01/09/2014 00:00:00And my goal is to get to this:
ITEMID Test1 Test2 Test3 Test4
12 mno def stu yz
14 123 NULL NULL NULLI created the following temp tables: -
@tempNames- contains a list of all of the distinctNames -@tempIDsthat contains all of the uniqueItemIDs -@tempInventorythat contains all of the desired schema shown above. How do I get one row of info for eachItemIDto the@tempInventorytable? (I don't even know what to call what I'm trying to do. Otherwise, I'd google it).".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013you are looking for a pivot query[^]
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
you are looking for a pivot query[^]
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
Awesome hint - fingered it out.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
