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
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
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.
declare @tableA table(id int, ref varchar(50))
declare @suffix table(id int, suffix varchar(50))
declare @todo table(id int)
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'
insert into @suffix
select * from @tableA
insert into @todo
select s1.id
from (
select id, cnt = count(*)
from @suffix
group by id, substring(suffix, 1, 1)
) s1
inner join (
select id, cnt = count(*)
from @suffix
group by id
) s2 on s2.id = s1.id and s2.cnt = s1.cnt
while exists (select * from @todo)
begin
update @suffix
set suffix = substring(suffix, 2, len(suffix) - 1)
from @suffix s
inner join @todo t on t.id = s.id
delete from @todo
insert into @todo
select s1.id
from (
select id, cnt = count(*)
from @suffix
group by id, substring(suffix, 1, 1)
) s1
inner join (
select id, cnt = count(*)
from @suffix
group by id
) s2 on s2.id = s1.id and s2.cnt = s1.cnt
end
select * from @suffix
