title: SQL Server 2008 split, sort and merge value
body: <p>I have a table in the following format (I'm using MS SQL Server 2008):</p>
  
  <pre><code>car_id | trace
  1        1300275738;57.72588;11.84981;0.00026388888888888886;1300275793;57.72596;11.8529;0.001055...
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>What I have tried to do is to split the value into separate rows in a temporary table like this:</p>
  
  <pre><code>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('&lt;A&gt;' + REPLACE(@SQLString, ';', '&lt;/A&gt;&lt;A&gt;') + '&lt;/A&gt;' AS XML)
  
  SELECT t.value('.', 'nvarchar(20)')
  FROM @x.nodes('/A') as x(t)
  </code></pre>
  
  <p>That gives the following result:</p>
  
  <pre><code>(No column name)
  1300275738
  57.72588
  11.84981
  0.000263888888888888
  1300275780
  57.72645
  11.85242
  0.001013888888888888
  .
  .
  </code></pre>
  
  <p>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? </p>