TSQL query to concatenate and remove common prefix

Question

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

Answer 1

WITH hier(cnt) AS
        (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt <= 100
        )
SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY id) = 1 THEN ref ELSE ' / ' + SUBSTRING(ref, mc + 1, LEN(ref)) END 
FROM    (
        SELECT  MIN(common) AS mc
        FROM    (
                SELECT  (
                        SELECT  MAX(cnt)
                        FROM    hier
                        WHERE   SUBSTRING(initref, 1, cnt) = SUBSTRING(ref, 1, cnt)
                                AND cnt <= LEN(ref)
                        ) AS common
                FROM    (
                        SELECT  TOP 1 ref AS initref
                        FROM    tableA
                        ) i,
                        tableA
                ) q
        ) q2, tableA
FOR XML PATH('')

---

3536757616 / 17 / 18 / 28 / 29 / 30

Same thing with groups:

WITH hier(cnt) AS
        (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt <= 100
        )
SELECT  (
        SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY a2.ref) = 1 THEN ref ELSE ' / ' + SUBSTRING(ref, mc + 1, LEN(ref)) END 
        FROM    tableA a2
        WHERE   a2.id = q2.id
        FOR XML PATH('')
        )
FROM    (
        SELECT  id, MIN(common) AS mc
        FROM    (
                SELECT  a.id,
                        (
                        SELECT  MAX(cnt)
                        FROM    hier
                        WHERE   SUBSTRING(i.initref, 1, cnt) = SUBSTRING(a.ref, 1, cnt)
                                AND cnt <= LEN(ref)
                        ) AS common
                FROM    (
                        SELECT  id, MIN(ref) AS initref
                        FROM    tableA
                        GROUP BY
                                id
                        ) i
                JOIN    tableA a
                ON      i.id = a.id
                ) q
        GROUP BY
                id
        ) q2
---
3536757616 / 7 / 8
3536757628 / 29 / 30

Answer 2

I named my table #T, and using the following SELECT statement

select id, number, substring(#t.ref, 1, v.number), count(id)
from master.dbo.spt_values v
inner join #t on v.number <= len(#t.ref)
where v.name is null and v.number > 0 
group by id, number, substring(#t.ref, 1, v.number)
order by id, count(id) desc, number desc

you get a result set where the first record of each id contains the maximum length and the longest initial string for each id.

That's not a complete solution, but a good starting point: iterate over the id's, issue a SELECT TOP 1 to retrieve the longest string, and concatenate the string diffs for each record with the same id.