I have some data
id ref
== ==========
1 3536757616
1 3536757617
1 3536757618
and want to get the result
1 3536757616/7/8
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
id ref
== ==========
2 3536757628
2 3536757629
2 3536757630
I would want to get the result
2 3536757629/28/30
I know I can simply concatenate the refs by using
SELECT distinct
id,
stuff ( ( SELECT
'/ ' + ref
FROM
tableA tableA_1
where tableA_1.id = tableA_2.id
FOR XML PATH ( '' ) ) , 1 , 2 , '' )
from TableA tableA_2
to give
1 3536757616/ 3536757617/ 3536757618
2 3536757628/ 3536757629/ 3536757630
but it's the bit that removes the common element that I'm after.....
Code for test data :
create table tableA (id int, ref varchar(50))
insert into tableA
select 1, 3536757616
union select 1, 3536757617
union select 1, 3536757618
union select 2, 3536757628
union select 2, 3536757629
union select 2, 3536757630