A bit of history to this : I posted this question elsewhere about 12 months ago, and got some very interesting and useful answers, but now I want to see what the wonderful community here can come up with.
--- In SQL 2005, I have some data
and want to get the result
so essentially the data is aggregated on id, with the refs concatenated together, separated by a slash '/', but with any common prefix removed so if the data was like
I would want to get the result
I know I can simply concatenate the refs by using
but it's the bit that removes the common element that I'm after.....
--- Code for test data :
This is a bit clunky... :)
Edited to include reference to a static tally table...
ORDER BY [mcl].[id];
I hope I understood you right, so here is my go at it. (v1.0 Lunchtime hack done in about 10 minutes and only tested on your example data).
Edit 14:37 CET - Fix added to comply with Kev's comment (extra CTE called MatchPos)
Edit 15:25 CET - Final fix so I really comply with Kev ;o)
Edit 15:39 CET - Kev really is playing with us! :o) Those repeating numbers made me reverse the position finding in the CTE LeftMostMatch, but it works now.
Edit 24-09-2010 09:26 - Made a further change to make sure that the results come out in the right order, even for permanent tables as opposed to table variables (order by added to the XML concatenation). The solution as a whole can be sped up by moving SplitRef into a permanent table (*Is that allowed?**).*
FROM Concatenation ;
As promised here's the answer I got originally, thanks to [Quassnoi over at StackOverflow]
Matt assures me that his solution is faster over a large enough data set, although I still marvel at the simplicity if this solution!
: h ttp://stackoverflow.com/questions/944160/tsql-query-to-concatenate-and-remove-common-prefix/944276#944276
answered Sep 30 '10 at 06:16 AM
Kev Riley ♦♦