There is a little problem for query to get something like a sum() for a character field to concatenate character values from all records in the group. Here is described solution.
The approach below have an limitation to max 30 records in group for concatenation. Test it if it will work for more records in group or there should be another limitation on your server, becaus ethis number is depended on SQL Server capabilities and length of the character field being concatenated.
The script works using a query like:
select T.*, rtrim(T.CharField) + ' ' + rtrim(IsNull(T1.CharField,''))+' '+ rtrim(IsNull(T2.CharField,''))+' '+ rtrim(IsNull(T3.CharField,'')) as CharResult from MyTable T left join MyTable T1 on T.GroupField=T1.GroupField AND T1.OrderField=T.OrderField+1 left join MyTable T2 on T.GroupField=T2.GroupField AND T2.OrderField=T1.OrderField+1 left join MyTable T3 on T.GroupField=T3.GroupField AND T3.OrderField=T2.OrderField+1 WHERE T.OrderField in (select min(TT.OrderField) from MyTable TT where TT.GroupField=T.GroupField)
CharField - field required to be concatenated, GroupField - field for grouping, OrderField - custom field that contains order number of record inside of the group.
As you see, required to use as many joins as you have records in the group. Such query require to be built on the fly because its number of joins is depended on how many records are concatenated. In addition, additional field is required in the table to maintain exact order number of each record inside of each group, the table will look like following:
ID CharField GroupField OrderField CharResult 1 'First' 'Item1' 1 'First Second' 3 'First' 'Item2' 1 'First Second Third Finish'
Following is the script that builds such query based on number of records in groups.
You can run it as a single SQL call to SQL Server, for example, using SQLEXEC() function in VFP (if you use VFP views, you can get connection handle using CursorGetProp('ConnectHandle','SomeViewAliasName')). You can also organize it as a stored procedure. To return data from it into another SP or T-SQL script, use INSERT command into a temporary table.
declare @maxnum int, @num int, @lcStr varchar(8000), @lcStr2 varchar(8000), @lcStr3 varchar(8000), @lcnum varchar(10) -- calculate the max number of records/group select @maxnum=max(cnt) from (select count(*) as cnt from MyTable group by GroupField) a
-- prepare SQl Statement in a string select @lcStr = 'select T0.*, convert(text,rtrim(T0.CharField)' select @lcStr2 = ') as CharResult from MyTable T0 ' select @lcStr3 = ' WHERE T0.OrderField in (select min(TT.OrderField) from MyTable TT where TT.GroupField=T0.GroupField)'
-- limitation. Correct this after testing on your SQL Server if @maxnum>30 select @maxnum=30
select @num=1 while @num<=@maxnum-1 begin select @lcnum = ltrim(rtrim(convert(varchar(10),@num))) select @lcStr = @lcStr + '+'' ''+rtrim(IsNull(T'+@lcnum+'.CharField,''''))' select @lcStr2 = @lcStr2 + ' left join MyTable T'+@lcnum+' on T0.GroupField=T'+@lcnum +'.GroupField AND T'+@lcnum+'.OrderField=T' + ltrim(rtrim(convert(varchar(10),@num-1))) +'.OrderField+1' select @num=@num+1 end
-- run a query from string execute (@lcStr + @lcStr2 + @lcStr3)
Finally, to populate the OrderField correctly after you added it, you can use following updating command:
Update MyTable SET MyTable.OrderField = (select count(*) from MyTable T where T.[ID]<MyTable.[ID] AND T.GroupField=MyTable.GroupField)+1
Where "ID" is unique identity key field in the table. It will assure that records in group all has correct order number. This updating command use the Identity key to sort records. however, youc an use another sorting order, for example, to sort strings in the result concatenated string in alphabetical order:
Update MyTable SET MyTable.OrderField = (select count(*) from MyTable T where T.GroupField=MyTable.GroupField AND T.CharField + convert(char(12), T.[ID]) < MyTable.CharField + convert(char(12), MyTable.[ID]) )+1
All above commands and scripts could make a good use of indexes by ID, GroupField and OrderField fields.