Find the count and indexes of occurrences of a string

Suppose given a string, how can I get the character, no. of occurrences and occurrence indexes in a single query?

Eg : Consultant

Character no of occurrences index:

C   1   1
O   1   2
N   2   3,9

Answer

You can use a recursive CTE to split it apart and then aggregate:

with cte as (
      select convert(varchar(max), null) as c, convert(varchar(max), 'Consultant') as rest, 0 as lev
      union all
      select left(rest, 1), stuff(rest, 1, 1, ''), lev + 1
      from cte
      where rest <> ''
     )
select c, count(*), string_agg(lev, ',')
from cte
where lev > 0
group by c;

Here is a db<>fiddle.