October 12, 2007 at 8:06 am
Hi
I have a result set that can bring back duplicate rows and I want to take information from the duplicated rows and conctenate them to the relevant field in the first row then removed the duplicated lines.
The reason behind this is I have a SP that returns a table such as
Name - Relation1 - Relation2 - Relation3
Paul - 23 - 24 - 45
john - 21 - NULL - 32
John - 21 - NULL - 36
as you can see john appears twice because although he only has a Relation 1 ID of 21 he has two Relation3's of ID 36 and ID 32, of course it could be more times.
This is expected the way the query is run and I can alter it so that John only shows one record by just say brong back the Max ID of Relation3 but I want to change this result set so that John is shown on one line and under Relation3 that Value is 32 ~ 36.
Can this be done using joins?
Other wise I was going to order by Name and place in a temp table with a PK. This would be selected in a cursor and the cursor will go through each row and were it encounters a new row with same name as previous it will update the previous row then delete the current row and so on.
Any advice appreciated
Thanks
Paul
October 12, 2007 at 8:15 am
Hi Paul,
under 2005 you could use a join, and use a trick with xml path to concatenate the column values.
Under 2000, where xml path is not supported you could create a user defined function to do the concatenation for you. There are examples for both of the above on http://www.sqlservercentral.com/Forums/Topic391111-338-1.aspx
Regards,
Andras
October 15, 2007 at 3:15 am
Thanks for pointing me in the right direction.
Regards
Paul
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply