SQL Server : coalesce, the part of string is missing

Question

I have this code:

declare @results varchar(500)

select 
    @results = coalesce(@results+', ', '') + convert(varchar(12),k.t1)
from
(
    select 
        '('+cast(count(distinct(g.RoomID)) as varchar) + ') '+ rt.ClassName as t1
    from 
        db_pms.Guests g
        left join db_pms.RoomTypes rt 
            on rt.RoomTypeID=g.RoomTypeID
    where 
        g.GroupID = 47 and 
        g.Status >= 0
    group by 
        g.RoomTypeID,
        rt.ClassName
) k

select @results as results

The part

select 
    '('+ cast(count(distinct(g.RoomID))as varchar) + ') '+ rt.ClassName as t1
from 
    db_pms.Guests g
    left join db_pms.RoomTypes rt 
        on rt.RoomTypeID=g.RoomTypeID
where
    g.GroupID = 47 and 
    g.Status >= 0
group by 
    g.RoomTypeID,
    rt.ClassName

returns

(1) Люкс
(4) Полулюкс
(2) Стандарт DBL
(6) Стандарт TWN
(1) Стандарт+ TWN

and after using

select @results = coalesce(@results + ', ', '') +  convert(varchar(12),k.t1)

I get

(1) Люкс
(4) Полулюкс
(2) Стандарт
(6) Стандарт
(1) Стандарт

As you can see a part of string column is missing. What can I do to fix it?

Answer 1

Your varchar(12) is too small to hold your results, due to the way MySQL (and SQL Server) counts length for varchar. For example, running this query:

SELECT LENGTH( 'Стандарт TWN' )

Will give you a length of 20! You need to increase the length, and you will be fine.

SQL Fiddle showing results: http://sqlfiddle.com/#!9/9eecb7d/5661/0

In SQL server you may wish to consider the use of nvarchar as well.