I think I've managed to get it. Here's the code I ended up using:

SELECTtbl2.iGroupId,

tbl1.vcSupplierId,

sum(tbl1.units) / tbl2.GroupUnits as share

FROM(SELECTROW_NUMBER() OVER(ORDER BY sq.vcItemSuppliers) iGroupId,

LEFT(sq.vcItemSuppliers,len(sq.vcItemSuppliers)-1) vcItemSuppliers,

sum(sq.itemunits) as GroupUnits

FROM(SELECToq.iItemId,

(SELECTt1.vcSupplierId+','

FROMtblOrder t1

WHEREt1.iItemId = oq.iItemId

FOR XML...

I was able to run this, although I had to essientially run the basic group by query as the insert into tblOrder for the actual query to have any chance...

Jason - thanks for the help. I'm running now. As long as it works optimization shouldn't be too much of an issue because I should only need to...

It's true that a basic group by gives the correct results on the sample data, but I have many more items supplied by the same group of suppliers. If...

Jason - that's an interesting result, thank you. I've never used For XML Path before, or anything with XML for that matter. From your results, I'm actually looking...

Thanks GSquared. The temp table is useful, and the results are interesting. When I ran I found:

- dateadd function 1266ms

- floor 781ms

- string 1687ms

All times are CPU.

String...

That scenario was the limitation with Chris's original reply (triggered by my limited data sample). It seems that Mark's application of CTE's and joining on all non-date columns did...

Thank you both for your assistance. It appears that Mark's solution works well (and better than the loop solution I had attempted). Chris - your solution worked exactly...