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?
;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
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
