将开始和结束列合并为一列 [英] Merge Start And End Columns To One Column
本文介绍了将开始和结束列合并为一列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经搜索了数周，试图找到解决我问题的方法.
I have searched high and low for weeks now trying to find a solution to my problem.
我的问题如下:
A 有一张表格，其中包含来自车辆遥测提供商的开始和结束坐标以及日期.
A have a table with start and end co-ordinates and dates from a vehicle telemetry provider.
我需要将这些合并到一列中，以便我们的报告解决方案能够绘制它们.
I need to merge these into one column in order for our reporting solution to be able to plot them.
部分示例数据如下:
DECLARE @TblVar AS TABLE([ServiceID] INT
,[StartDate] DATETIME
,[StartLocation] VARCHAR(255)
,[StartLat] FLOAT
,[StartLong] FLOAT
,[EndDate] DATETIME
,[EndLocation] VARCHAR(255)
,[EndLat] FLOAT
,[EndLong] FLOAT);
INSERT INTO @TblVar
VALUES (48322, '2016-11-28 05:38:37.000','Weaver Road,Leicester',52.5753273,-1.306842,'2016-11-28 05:40:02.000','Earl Street,Leicester',52.5781,-1.3048711)
,(48322, '2016-11-28 05:44:05.000','Earl Street,Leicester',52.5780996,-1.3048768,'2016-11-28 05:53:52.000','Arbor Road,Leicester',52.5533448,-1.2349645)
,(48322, '2016-11-28 09:14:25.000','Arbor Road,Leicester',52.5533833,-1.2349445,'2016-11-28 09:15:54.000','Coventry Road,Leicester',52.5522865,-1.2370495)
,(48322, '2016-11-28 09:16:05.000','Coventry Road,Leicester',52.552292,-1.2370598,'2016-11-28 09:20:41.000','Arbor Road,Leicester',52.553338,-1.2352033)
,(48322, '2016-11-28 13:34:36.000','Arbor Road,Leicester',52.553388,-1.2352651,'2016-11-28 13:53:29.000','Narborough Road South,Leicester',52.597422,-1.1832976)
,(48322, '2016-11-28 13:55:36.000','Narborough Road South,Leicester',52.597352,-1.183299,'2016-11-28 13:58:51.000','Hazel Drive,Leicester',52.6020946,-1.1760238)
,(48322, '2016-11-28 14:11:53.000','Hazel Drive,Leicester',52.6020853,-1.1760053,'2016-11-28 14:24:40.000','Arbor Road,Leicester',52.5533118,-1.2352118)
,(48322, '2016-11-28 14:29:48.000','Arbor Road,Leicester',52.5532741,-1.2352471,'2016-11-28 14:30:39.000','Coventry Road,Leicester',52.552955,-1.2363475)
,(48322, '2016-11-28 14:32:24.000','Coventry Road,Leicester',52.552944,-1.2363491,'2016-11-28 14:34:06.000','Coventry Road,Leicester',52.5532598,-1.2350731)
,(48322, '2016-11-28 14:50:12.000','Coventry Road,Leicester',52.5532646,-1.2351661,'2016-11-28 14:50:13.000','Coventry Road,Leicester',52.5532646,-1.2351661)
,(48322, '2016-11-28 16:02:29.000','Coventry Road,Leicester',52.553156,-1.2348643,'2016-11-28 16:13:09.000','Earl Street,Leicester',52.5780946,-1.30486)
,(48322, '2016-11-28 16:22:07.000','Earl Street,Leicester',52.5780776,-1.304851,'2016-11-28 16:23:26.000','Weaver Road,Leicester',52.5753643,-1.306814)
看起来像这样:
+-----------+---------------------+---------------------------------+------------+------------+---------------------+---------------------------------+------------+------------+
| ServiceID | StartDate | StartLocation | StartLat | StartLong | EndDate | EndLocation | EndLat | EndLong |
+-----------+---------------------+---------------------------------+------------+------------+---------------------+---------------------------------+------------+------------+
| 48322 | 28/11/2016 05:38:37 | Weaver Road,Leicester | 52.5753273 | -1.306842 | 28/11/2016 05:40:02 | Earl Street,Leicester | 52.5781 | -1.3048711 |
| 48322 | 28/11/2016 05:44:05 | Earl Street,Leicester | 52.5780996 | -1.3048768 | 28/11/2016 05:53:52 | Arbor Road,Leicester | 52.5533448 | -1.2349645 |
| 48322 | 28/11/2016 09:14:25 | Arbor Road,Leicester | 52.5533833 | -1.2349445 | 28/11/2016 09:15:54 | Coventry Road,Leicester | 52.5522865 | -1.2370495 |
| 48322 | 28/11/2016 09:16:05 | Coventry Road,Leicester | 52.552292 | -1.2370598 | 28/11/2016 09:20:41 | Arbor Road,Leicester | 52.553338 | -1.2352033 |
| 48322 | 28/11/2016 13:34:36 | Arbor Road,Leicester | 52.553388 | -1.2352651 | 28/11/2016 13:53:29 | Narborough Road South,Leicester | 52.597422 | -1.1832976 |
| 48322 | 28/11/2016 13:55:36 | Narborough Road South,Leicester | 52.597352 | -1.183299 | 28/11/2016 13:58:51 | Hazel Drive,Leicester | 52.6020946 | -1.1760238 |
| 48322 | 28/11/2016 14:11:53 | Hazel Drive,Leicester | 52.6020853 | -1.1760053 | 28/11/2016 14:24:40 | Arbor Road,Leicester | 52.5533118 | -1.2352118 |
| 48322 | 28/11/2016 14:29:48 | Arbor Road,Leicester | 52.5532741 | -1.2352471 | 28/11/2016 14:30:39 | Coventry Road,Leicester | 52.552955 | -1.2363475 |
| 48322 | 28/11/2016 14:32:24 | Coventry Road,Leicester | 52.552944 | -1.2363491 | 28/11/2016 14:34:06 | Coventry Road,Leicester | 52.5532598 | -1.2350731 |
| 48322 | 28/11/2016 14:50:12 | Coventry Road,Leicester | 52.5532646 | -1.2351661 | 28/11/2016 14:50:13 | Coventry Road,Leicester | 52.5532646 | -1.2351661 |
| 48322 | 28/11/2016 16:02:29 | Coventry Road,Leicester | 52.553156 | -1.2348643 | 28/11/2016 16:13:09 | Earl Street,Leicester | 52.5780946 | -1.30486 |
| 48322 | 28/11/2016 16:22:07 | Earl Street,Leicester | 52.5780776 | -1.304851 | 28/11/2016 16:23:26 | Weaver Road,Leicester | 52.5753643 | -1.306814 |
+-----------+---------------------+---------------------------------+------------+------------+---------------------+---------------------------------+------------+------------+
我想要的输出如下:
+-----------+-----------+---------------------+---------------------------------+------------+------------+
| ServiceID | PathOrder | Date | Location | Lattitude | Longitude |
+-----------+-----------+---------------------+---------------------------------+------------+------------+
| 48322 | 1 | 28/11/2016 05:38:37 | Weaver Road,Leicester | 52.5753273 | -1.306842 |
| 48322 | 2 | 28/11/2016 05:40:02 | Earl Street,Leicester | 52.5781 | -1.3048711 |
| 48322 | 3 | 28/11/2016 05:44:05 | Earl Street,Leicester | 52.5780996 | -1.3048768 |
| 48322 | 4 | 28/11/2016 05:53:52 | Arbor Road,Leicester | 52.5533448 | -1.2349645 |
| 48322 | 5 | 28/11/2016 09:14:25 | Arbor Road,Leicester | 52.5533833 | -1.2349445 |
| 48322 | 6 | 28/11/2016 09:15:54 | Coventry Road,Leicester | 52.5522865 | -1.2370495 |
| 48322 | 7 | 28/11/2016 09:16:05 | Coventry Road,Leicester | 52.552292 | -1.2370598 |
| 48322 | 8 | 28/11/2016 09:20:41 | Arbor Road,Leicester | 52.553338 | -1.2352033 |
| 48322 | 9 | 28/11/2016 13:34:36 | Arbor Road,Leicester | 52.553388 | -1.2352651 |
| 48322 | 10 | 28/11/2016 13:53:29 | Narborough Road South,Leicester | 52.597422 | -1.1832976 |
| 48322 | 11 | 28/11/2016 13:55:36 | Narborough Road South,Leicester | 52.597352 | -1.183299 |
| 48322 | 12 | 28/11/2016 13:58:51 | Hazel Drive,Leicester | 52.6020946 | -1.1760238 |
| 48322 | 13 | 28/11/2016 14:11:53 | Hazel Drive,Leicester | 52.6020853 | -1.1760053 |
| 48322 | 14 | 28/11/2016 14:24:40 | Arbor Road,Leicester | 52.5533118 | -1.2352118 |
| 48322 | 15 | 28/11/2016 14:29:48 | Arbor Road,Leicester | 52.5532741 | -1.2352471 |
| 48322 | 16 | 28/11/2016 14:30:39 | Coventry Road,Leicester | 52.552955 | -1.2363475 |
| 48322 | 17 | 28/11/2016 14:32:24 | Coventry Road,Leicester | 52.552944 | -1.2363491 |
| 48322 | 18 | 28/11/2016 14:34:06 | Coventry Road,Leicester | 52.5532598 | -1.2350731 |
| 48322 | 19 | 28/11/2016 14:50:12 | Coventry Road,Leicester | 52.5532646 | -1.2351661 |
| 48322 | 20 | 28/11/2016 14:50:13 | Coventry Road,Leicester | 52.5532646 | -1.2351661 |
| 48322 | 21 | 28/11/2016 16:02:29 | Coventry Road,Leicester | 52.553156 | -1.2348643 |
| 48322 | 22 | 28/11/2016 16:13:09 | Earl Street,Leicester | 52.5780946 | -1.30486 |
| 48322 | 23 | 28/11/2016 16:22:07 | Earl Street,Leicester | 52.5780776 | -1.304851 |
| 48322 | 24 | 28/11/2016 16:23:26 | Weaver Road,Leicester | 52.5753643 | -1.306814 |
+-----------+-----------+---------------------+---------------------------------+------------+------------+
任何想法/代码都会很受欢迎.
Any ideas / code will be greatly received.
谢谢，
丹
推荐答案
通过使用下面的方法，您可以获得想要的结果.您可以将 Cross apply 与 values 子句一起使用.
By using below you can get the desired results. You can use Cross apply with values clause.
SELECT A.SERVICEID,
ROW_NUMBER() OVER(PARTITION BY A.SERVICEID ORDER BY A.SERVICEID) AS PATHORDER,
B.*
FROM @TBLVAR A
CROSS APPLY(
VALUES
([STARTDATE],[STARTLOCATION],[STARTLAT],[STARTLONG]),
([ENDDATE],[ENDLOCATION],[ENDLAT],[ENDLONG])
)B(DATE, LOCATION, LATTITUDE, LONGITUD)
这篇关于将开始和结束列合并为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
