How do you count and list number of occurrences of a phrase or word separated by a semicolon in a string in SQL?

I have a column that has values formatted as shown in quotes.

"Promotion - External; Outside Hire; Reassignment - External; Promotion - External; Promotion - External; Promotion - External; Promotion - External; Outside Hire; Promotion - External"

How do I format such that it displays as shown in the results in quotes using SSQL Server 2016?

"6 Promotion - External; 2 Outside Hire; 1 Reassignment - External"

Answer

This should work for you

declare @string as varchar(1000), @result varchar(500)

set @string = '"Promotion - External; Outside Hire; Reassignment - External; Promotion - External; Promotion - External; Promotion - External; Promotion - External; Outside Hire; Promotion - External"'
set @string = stuff(stuff(@string,1,1,''), len(@string)-1,1,'')

;with data as
(
    select rtrim(ltrim(i.value('.', 'varchar(max)'))) string
    from 
    (
        select cast('<M>' + replace(@string, ';', '</M><M>') + '</M>' as xml) as info
    ) as d
    cross apply d.info.nodes ('/M') AS Split(i)
), final_result as
(
    select string, count(string) num
    from data
    group by string
)
select @result = 
concat('"',
        stuff(
                (
                    select concat('; ', cast(num as varchar), ' ', string) 
                    from final_result 
                    order by num desc
                    for xml path('')
                )
            , 1, 2, '')
        , '"')

select @result