-1

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"
Ropeh
  • 43
  • 6
  • 1
    Split in into rows (how it's done depends on your DBMS), GROUP BY it and finally apply listagg (again, how it's done depends on your DBMS) – dnoeth Sep 17 '20 at 15:31

2 Answers2

1

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
Mova
  • 928
  • 1
  • 6
  • 23
0

In MS SQL:

DECLARE @text varchar(max) = 'Promotion - External; Outside Hire; Reassignment - External; Promotion - External; Promotion - External; Promotion - External; Promotion - External; Outside Hire; Promotion - External';

select string_agg(concat(c,' ',v),';') 
from  (
    select count(*) as c, trim(value) v
    from string_split(@text,';')
    group by trim(value)
) x;

output:

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

or, if you want the output in the same order, like:

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

then you could try:

select string_agg(concat(c,' ',v),';') within group (order by c desc)
from  (
    select count(*) as c, trim(value) v
    from string_split(@text,';')
    group by trim(value)
) x;
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • For some reason SQL Server 2016 will not permit the use of functions string aggregate and 'trim'. Does anyone know why these errors are thrown in ? – Ropeh Sep 17 '20 at 17:51
  • [TRIM](https://stackoverflow.com/questions/179625/how-to-trim-a-string-in-sql-server-before-2017) works from version 2017 see link for how to do it in earlier versions. The same goes for [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) if you search you will find a solution for earlier versions of MS SQL. – Luuk Sep 18 '20 at 06:11
  • [STRING_AGG](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) for earlier versions. (Why does one find the link just after hitting an enter key.... ) – Luuk Sep 18 '20 at 06:14