Click here to Skip to main content
15,867,921 members
Home / Discussions / Database
   

Database

 
AnswerRe: Remove duplicate rows in multiple join query Pin
Chris Quinn25-Mar-14 22:02
Chris Quinn25-Mar-14 22:02 
AnswerRe: Remove duplicate rows in multiple join query Pin
Member 1071490931-Mar-14 22:55
Member 1071490931-Mar-14 22:55 
QuestionOracle Error Pin
Member 867725124-Mar-14 19:49
Member 867725124-Mar-14 19:49 
AnswerRe: Oracle Error Pin
Kornfeld Eliyahu Peter24-Mar-14 20:13
professionalKornfeld Eliyahu Peter24-Mar-14 20:13 
GeneralRe: Oracle Error Pin
Member 867725124-Mar-14 20:38
Member 867725124-Mar-14 20:38 
GeneralRe: Oracle Error Pin
Jörgen Andersson24-Mar-14 21:34
professionalJörgen Andersson24-Mar-14 21:34 
AnswerRe: Oracle Error Pin
Member 1043154927-Mar-14 0:28
Member 1043154927-Mar-14 0:28 
QuestionComplex Query Pin
#realJSOP24-Mar-14 4:18
mve#realJSOP24-Mar-14 4:18 
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 different CollectedDate

For a given ItemID, I need to return the Value with the latest CollectedDate for each distinct Name. To add to the complexity, I need to put all of the returned items into a single row, using the Name as the column name.

So, what I'm starting with is this:

txt
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:00


And my goal is to get to this:

txt
ITEMID  Test1   Test2   Test3   Test4
12      mno     def     stu     yz
14      123     NULL    NULL    NULL



I created the following temp tables:

- @tempNames - contains a list of all of the distinct Names
- @tempIDs that contains all of the unique ItemIDs
- @tempInventory that contains all of the desired schema shown above.

How do I get one row of info for each ItemID to the @tempInventory table?

(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


AnswerRe: Complex Query Pin
Simon_Whale24-Mar-14 4:46
Simon_Whale24-Mar-14 4:46 
GeneralRe: Complex Query Pin
#realJSOP24-Mar-14 5:53
mve#realJSOP24-Mar-14 5:53 
QuestionGrouping Results by ElectionName Pin
samflex19-Mar-14 10:44
samflex19-Mar-14 10:44 
AnswerRe: Grouping Results by ElectionName Pin
Jörgen Andersson19-Mar-14 11:02
professionalJörgen Andersson19-Mar-14 11:02 
AnswerRe: Grouping Results by ElectionName Pin
Mycroft Holmes19-Mar-14 12:52
professionalMycroft Holmes19-Mar-14 12:52 
GeneralRe: Grouping Results by ElectionName Pin
samflex19-Mar-14 13:12
samflex19-Mar-14 13:12 
GeneralRe: Grouping Results by ElectionName Pin
Mycroft Holmes19-Mar-14 14:05
professionalMycroft Holmes19-Mar-14 14:05 
GeneralRe: Grouping Results by ElectionName Pin
samflex19-Mar-14 14:13
samflex19-Mar-14 14:13 
GeneralSSIS - Truncation may occur due to inserting data Pin
Smith201418-Mar-14 2:59
Smith201418-Mar-14 2:59 
AnswerRe: SSIS - Truncation may occur due to inserting data Pin
Kornfeld Eliyahu Peter18-Mar-14 3:00
professionalKornfeld Eliyahu Peter18-Mar-14 3:00 
GeneralRe: SSIS - Truncation may occur due to inserting data Pin
Smith201418-Mar-14 3:07
Smith201418-Mar-14 3:07 
AnswerRe: SSIS - Truncation may occur due to inserting data Pin
Kornfeld Eliyahu Peter18-Mar-14 3:11
professionalKornfeld Eliyahu Peter18-Mar-14 3:11 
GeneralRe: SSIS - Truncation may occur due to inserting data Pin
jschell18-Mar-14 8:44
jschell18-Mar-14 8:44 
GeneralRe: SSIS - Truncation may occur due to inserting data Pin
Mycroft Holmes18-Mar-14 12:55
professionalMycroft Holmes18-Mar-14 12:55 
QuestionSSIS - Truncation may occur due to inserting data Pin
Smith201418-Mar-14 2:54
Smith201418-Mar-14 2:54 
QuestionInserting a new record into 2 tables at the same time Pin
jkirkerx17-Mar-14 11:49
professionaljkirkerx17-Mar-14 11:49 
AnswerRe: Inserting a new record into 2 tables at the same time Pin
Mycroft Holmes17-Mar-14 19:48
professionalMycroft Holmes17-Mar-14 19:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.