>>Hi All -
>>
>>It seems like I should know how to do this but for some reason I am drawing a blank. Suppose you have 2 tables in a 1 to many raltionship. Table1 contains data about various businesses.
>>Table2 (child) contains notes (memo field) about those businesses.
>>How do you write an sql that will sum the notes into a single field so that we end up with 1 record per business including a field with all of the notes?
>>
>>Something like
>>
>>SELECT table1.*, sum(table2.Notes) as Notes ;
>>FROM table1;
>>left join table2 ON key . . . .
>>
>>
>> I might add that this is VFP data exposed on a asp website. The end game is to export all of the data to excel.
>>Thanks for any help on this.
>>I'm stumped. :)
>
>In VFP the only way will be to create a custom UDF that will concatenate notes. I suggest to simply scan the table instead and create the concatenated Notes field.
>
>In SQL Server you will use XML PATH('') approach for this.
Hi Naomi -
I have moved the data into SQL Server 2008 and the application is being ported to VB.NET. I have read the docs on xml path (very confusing) and I don't think it meets my need.. What I would like to do is create a select statement for use in a sqldatasource that will produce the desired result. The datasource will be used to populate a gridview. So I was wondering if a stored procedure could be written that would assemble the desired combined notes field, and could be included in the SELECT statement. Alternatively maybe I could build the datasource in code with a temporary table? The workflow here is: User defines a query, Query is executed and results displayed in a Gridview, Gridview is exported to eXcel so he can take the data on the road.
Ultimately there will be several tables involved:
Projects
Business' affected by the projects (1 to many)
Notes about the business' (1 to many)
Business2People (bridge table for many to many)
People who belong to the business'
The result set will have 1 record per business with a field combining the multiple notes and a second field combining the multiple people.
Thoughts?
