SQL Server 2008拆分，排序和合并值 [英] SQL Server 2008 split, sort and merge value

问题描述

我有一个以下格式的表(我正在使用MS SQL Server 2008):

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...

跟踪值是一个以分号作为分隔符的csv字符串.跟踪字符串中的值分为四个和四个分组，如下所示(除了没有换行符):

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)

得出以下结果:

(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

在 SQLFiddle

这篇关于SQL Server 2008拆分，排序和合并值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！