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)
ID CharField GroupField OrderField ...Other fields... 1 'First' 'Item1' 1 2 'Second' 'Item1' 2 3 'First' 'Item2' 1 4 'Second' 'Item2' 2 5 'Third' 'Item2' 3 6 'Finish' 'Item2' 4
ID CharField GroupField OrderField CharResult 1 'First' 'Item1' 1 'First Second' 3 'First' 'Item2' 1 'First Second Third Finish'
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)
Update MyTable SET MyTable.OrderField = (select count(*) from MyTable T where T.[ID]<MyTable.[ID] AND T.GroupField=MyTable.GroupField)+1
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