Issue with merging of rows in sql server

Question

I have tried this below code

select STUFF((
        select ',' + t1 Issue
        from Log_table  t1
        where t1.VID= t.VID
        for xml path(''), type  
       ).value('.', 'nvarchar(max)'), 1, 2, '')  Cmnts,
       Main_table .vehical_id, name, location,
from Log_table t RIGHT JOIN
     main_table
     on VID = vehicle_id
group by t.VID, Vehicle_id, name, location**

after running the query the data in "issue" column is not in proper order. means for example for vehicle_id-333 .

how make correct allignment for this.

thanks.

Answer 1

please try below query. below we are introducing ORDER over comment field. demo sql fiddle link: http://sqlfiddle.com/#!6/981c34/1

select  
m.vehicle_id, 
m.name, 
m.location,
STUFF(
        (
            select ',' + t.issue from
                    (
                        select *, 
                            row_number() over (partition by vid order by issue asc) as r 
                        from Log_table 
                    ) t
            where t.VID= m.vehicle_id 
                order by t.r
            for xml path(''), type  
        ).value('.', 'nvarchar(max)'), 1, 2, '')  
        Cmnts
from
     main_table m

Answer 2

Why are you referring to the log_table twice?

Second, SQL tables represent unordered sets. You need a separate column to specify the ordering. Let me assume that you have a log_id column in the leg_table with this information:

select STUFF((select ',' + t.Issue
              from Log_table t
              where t.VID = m.VID
              order by t.log_id  -- Assumes you have an id or some column for ordering
              for xml path(''), type  
             ).value('.', 'nvarchar(max)'), 1, 2, ''
            )  Cmnts, m.vehicle_id, m.name, m.location,
from main_table m;

I removed the group by, because you probably don't need that.