문자열 aaaaaaaaabbbca를 a9b3c1a1와 같이 압축하고 싶다.

declare 
    @s varchar(200)
,   @result varchar(200) = ''
set @s = 'aaaaaaaaabbbca'

;with dummy
as
(
        select 1 seq
        union all
        select seq + 1 from dummy
        where seq + 1 <= 1000
)
select
    @result = @result + concat(s, count(*))
from (
    select 
        substring(@s, seq, 1) s
    ,   seq
    ,   seq - row_number() over(partition by substring(@s, seq, 1) order by seq) grp
    from dummy
    where seq <= len(@s)
) t
group by
    grp
,   s
order by min(seq)
option (maxrecursion 0);

select @result 압축문자열, len(@result) 길이