October 5, 2008 at 10:11 am
Table 1
seqnumNotes
1 Note1
2 note2
Table 2
seqnumnotes
1notesA
1notesB
2notesC
2notesD
2notesE
Seqnum is unique in table one.
So now record 1 has two new notes in table two that needs to be appended to Note1 in table 1 in the same column.
How do I append the two new notes for seqnum 1 in table 2 to table one (Note1)?
October 5, 2008 at 10:57 am
I think you have a few different options.
1. run a loop on table 1 and create a comma delimited string containing all the values for that record in table 2 (not a good idea. row by row processing is not recommended)
2. Create a scalar function that takes the ID of the record in table1 and creates a string containing all the notes of that ID in Table2.
3. If you are in SQL Server 2005, you can use FOR XML PATH as shown in this example: http://www.sqlserverandxml.com/2008/08/how-to-generate-delimited-string-using.html
.
October 6, 2008 at 2:11 am
Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
Failing to plan is Planning to fail
October 6, 2008 at 5:40 am
o you know how to use FOR XML PATH ?
October 6, 2008 at 5:42 am
Can you help me with a scaler function that will work with the table structures I posted, please? I'm a newbie and not too familiar with string functions.
October 6, 2008 at 5:46 am
Franco_1 (10/6/2008)
o you know how to use FOR XML PATH ?
Franco,
This article shows an example using FOR XML path.
http://www.sqlserverandxml.com/2008/08/how-to-generate-delimited-string-using.html
.
October 6, 2008 at 6:43 am
Jacob,
Thank you very much. The FOR XML PATH link you directed
me to worked like magic.
Thank you. U made my WEEK!!!!!:)
October 6, 2008 at 6:44 am
Glad to know it helped 🙂
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply