July 9, 2007 at 7:56 am
Hello All
I have a table with the following sample records
ID, Time, user, Event
The ID column uniquely identifies the event. The table below is for illustration purposes only and does not represent the true table in question.
ID Time User Event
5 2/5/2005 Tack Delete
5 2/5/2005 Trip Delete
5 2/5/2005 Grays Delete
5 2/5/2005 Jets Delete
6 2/5/2005 Ty Update
7 2/5/2005 TKIP Remove
I need a way to do the following;
Merge the users Tack, trip, Grays, Jets into one column with the values separated by comma
Merge all records with ID 5 into one row. My output should look like this;
ID Time User Event
5 2/5/2005 Tack, trip, Grays, Jets Delete
This will be done for all records in the table.
Reason is that I need to use ID as the primary key
Any help will be greatly appreciated
July 9, 2007 at 9:55 am
So many places to go on this, and so little time... Some thoughts:
1. Assuming that the ID will be unique, and you don't want to lose data, there are outstanding posts and links that will show you how to construct the comma-delimited lists. Also, check for purging 'duplicate records' (for instance, after restoring twice, introducing duplicate records). I apologize for not having the links at my fingertips.
2. Always go back and examine the assumptions. Is this a case of modifying table definition because of change in intent, or was it simply an oversight? Either way, going forward, will it be meaningful to 'concatenate' future additions to this primary key? Or is the primary key selection suspect?
3. How are these records created? Have you control over the process(es) that create new records? Or will this be a recurring problem, and your attempt at discipline (by adding a primary key) is to force the programmers to go back and modify their code?
4. 'Add', 'Update', 'Remove' - are these real attributes, or just chosen to distinguish between values, such as 'A', 'B', and 'C' would have been equally effective? Where I'm going with this question is to suggest that the primary key selection seems a dubious choice.
As stated above, just some thoughts on the subject. Point 1 is addressed directly to get you where you stated you wanted to get to. I'm just not convinced that you will be happy with those results.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply