SQL Server 2008 split, sort and merge value

Question

I have a table in the following format (I'm using MS SQL Server 2008):

car_id | trace
1        1300275738;57.72588;11.84981;0.00026388888888888886;1300275793;57.72596;11.8529;0.001055...

The trace value is a csv string with semicolon as deliminator. The values in the trace string are grouped four and four, like this (except no linebreaks):

1300275738;57.72588;11.84981;0.00026388888888888886;
1300275793;57.72596;11.8529;0.0010555555555555555;
1300275785;57.72645;11.85242;0.007416666666666665;
1300275780;57.72645;11.85242;0.0010138888888888888;

What I want to do is to create a trigger on insert that sorts the trace string for based on the first value in the groups of four. So the result of the above would become

1300275738;57.72588;11.84981;0.00026388888888888886;1300275780;57.72645;11.85242;0.0010138888888888888;1300275785;57.72645;11.85242;0.007416666666666665;1300275793;57.72596;11.8529;0.0010555555555555555;

What I have tried to do is to split the value into separate rows in a temporary table like this:

USE tempdb
GO

checkpoint
dbcc dropcleanbuffers
dbcc freeproccache
GO

--declare a variable and populate it with a comma separated string
DECLARE @SQLString VARCHAR(MAX)
SET @SQLString = (SELECT trace FROM mypev_trips.dbo.trips)

--append a comma to the string to get correct results with empty strings or strings with a single value (no commas)
SET @SQLString = @SQLString + ';';

DECLARE @X XML
SET @X = CAST('<A>' + REPLACE(@SQLString, ';', '</A><A>') + '</A>' AS XML)

SELECT t.value('.', 'nvarchar(20)')
FROM @x.nodes('/A') as x(t)

That gives the following result:

(No column name)
1300275738
57.72588
11.84981
0.000263888888888888
1300275780
57.72645
11.85242
0.001013888888888888
.
.

Does anyone know how I can transform my temporary table back to a comma separated string sorted on the first value in each group of four?

Answer 1

I ended up using two functions and a trigger.

First a function to split the trace string into rows

CREATE FUNCTION dbo.Split(@String varchar(MAX))       
returns @temptable TABLE (items varchar(MAX)) 
as       
begin       
    declare @idx int       
    declare @idx2 int
    declare @idx3 int
    declare @idx4 int
    declare @slice varchar(8000)

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx != 0       
    begin       
        set @idx = charindex(';',@String)       
        if @idx!=0       
            set @slice = left(@String,@idx)       
        else       
            set @slice = @String       

        set @String = right(@String,len(@String) - @idx)
        set @idx2 = charindex(';',@String);

        if @idx2!=0       
            set @slice = @slice + left(@String,@idx2)

        set @String = right(@String,len(@String) - @idx2)
        set @idx3 = charindex(';',@String);

        if @idx3!=0       
            set @slice = @slice + left(@String,@idx3)

        set @String = right(@String,len(@String) - @idx3)
        set @idx4 = charindex(';',@String);

        if @idx4 !=0       
            set @slice = @slice + left(@String,@idx4)
        else
            set @slice = @slice + @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx4) 
        if len(@String) = 0 break
        if @idx4 = 0 break   
    end
return      
end

Then a function to sort and merge the rows from the temporary table

CREATE FUNCTION dbo.SortCSV(@String varchar(MAX))       
returns varchar(MAX)
as
begin
    declare @csv varchar(MAX)
    select @csv = coalesce(@csv,'') + items
    from dbo.Split(@String)
    order by items asc
return @csv
end

And at last I created a trigger like this

CREATE TRIGGER dbo.SortTrace 
   ON  dbo.trips 
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @trace varchar(MAX);
    DECLARE @sorted_trace varchar(MAX);

    SELECT @trace = trace FROM inserted;
    SELECT @sorted_trace = dbo.SortCSV(@trace);

    insert into dbo.trips (
        car_id, start, stop, distance, created_at, updated_at, trace, energy, delta_soc, private_trip, journal_comment
    ) select
        car_id, start, stop, distance, created_at, updated_at, @sorted_trace, energy, delta_soc, private_trip, journal_comment
    from
        inserted
END
GO

Answer 2

;WITH cte(car_Id, traceXML) AS
 (
  SELECT car_Id, CAST('<A>' + REPLACE(trace, ';', '</A><A>') + '</A>' AS XML)
  FROM dbo.cars
  ), cte2 AS
 (
  SELECT car_Id, trace, NTILE(4) OVER(ORDER BY (SELECT 1)) AS grId  
  FROM cte
  CROSS APPLY (SELECT Tbl.Col.value('.', 'nvarchar(250)') AS trace 
               FROM traceXML.nodes('/A') Tbl(Col)) AS List
  ), cte3 AS
 (
  SELECT DISTINCT a.car_Id,(
  SELECT ISNULL(b.trace, '') + ';' 
  FROM cte2 b 
  WHERE b.grId = a.grId
  FOR XML PATH('')) AS trace
  FROM cte2 a
  )
  SELECT DISTINCT a.car_Id,(
  SELECT ISNULL(b.trace, '')
  FROM cte3 b
  WHERE b.car_Id = a.car_Id
  FOR XML PATH('')) AS trace
  FROM cte3 a

Demo on SQLFiddle