当前位置： 文江博客话题详情

在 TSQL 中进行旋转

发布于 2025-01-02 22:42:11 字数 9077 浏览 5 评论 0原文

我有看起来像这样的原始数据

在此处输入图像描述

我想要旋转报告看起来像这样

在此处输入图像描述

如何像这样旋转它在 TSQL 中，开始时间的顺序是从左到右？

这是生成数据的 TSQL

Declare @ProcessHistory Table ( Id Int, ProcessId Int, LocationId Int, StartTime DateTime, EndTime DateTime, Duration Time (0) )
Insert Into @ProcessHistory Values
(2, 13, 0, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.963', '00:00:00'),
(3, 13, 10, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.633', '00:00:00'),
(4, 13, 20, '2012-01-27 12:23:35.633', '2012-01-27 12:23:35.633', '00:00:00'),
(5, 13, 30, '2012-01-27 12:23:35.633', '2012-01-27 12:23:35.657', '00:00:00'),
(6, 13, 40, '2012-01-27 12:23:35.657', '2012-01-27 12:23:35.847', '00:00:00'),
(7, 13, 50, '2012-01-27 12:23:35.847', '2012-01-27 12:23:35.950', '00:00:00'),
(8, 13, 60, '2012-01-27 12:23:35.950', '2012-01-27 12:23:35.963', '00:00:00'),
(218, 13, 0, '2012-01-27 22:05:34.650', '2012-01-27 22:05:37.297', '00:00:03'),
(219, 13, 10, '2012-01-27 22:05:34.653', '2012-01-27 22:05:35.453', '00:00:01'),
(220, 13, 20, '2012-01-27 22:05:35.457', '2012-01-27 22:05:35.463', '00:00:00'),
(221, 13, 30, '2012-01-27 22:05:35.467', '2012-01-27 22:05:35.570', '00:00:00'),
(222, 13, 40, '2012-01-27 22:05:35.570', '2012-01-27 22:05:35.620', '00:00:00'),
(223, 13, 50, '2012-01-27 22:05:35.620', '2012-01-27 22:05:37.280', '00:00:02'),
(224, 13, 60, '2012-01-27 22:05:37.283', '2012-01-27 22:05:37.293', '00:00:00'),
(434, 13, 0, '2012-01-29 04:35:32.370', '2012-01-29 04:35:36.913', '00:00:04'),
(435, 13, 10, '2012-01-29 04:35:32.400', '2012-01-29 04:35:33.817', '00:00:01'),
(436, 13, 20, '2012-01-29 04:35:33.883', '2012-01-29 04:35:33.933', '00:00:00'),
(437, 13, 30, '2012-01-29 04:35:33.933', '2012-01-29 04:35:34.153', '00:00:01'),
(438, 13, 40, '2012-01-29 04:35:34.180', '2012-01-29 04:35:34.660', '00:00:00'),
(439, 13, 50, '2012-01-29 04:35:34.727', '2012-01-29 04:35:36.700', '00:00:02'),
(440, 13, 60, '2012-01-29 04:35:36.763', '2012-01-29 04:35:36.853', '00:00:00'),
(650, 13, 0, '2012-01-29 22:19:18.297', '2012-01-29 22:19:22.103', '00:00:04'),
(651, 13, 10, '2012-01-29 22:19:18.297', '2012-01-29 22:19:19.450', '00:00:01'),
(652, 13, 20, '2012-01-29 22:19:19.453', '2012-01-29 22:19:19.473', '00:00:00'),
(653, 13, 30, '2012-01-29 22:19:19.473', '2012-01-29 22:19:19.493', '00:00:00'),
(654, 13, 40, '2012-01-29 22:19:19.493', '2012-01-29 22:19:19.537', '00:00:00'),
(655, 13, 50, '2012-01-29 22:19:19.537', '2012-01-29 22:19:22.073', '00:00:03'),
(656, 13, 60, '2012-01-29 22:19:22.073', '2012-01-29 22:19:22.083', '00:00:00'),
(866, 13, 0, '2012-01-30 11:09:21.437', '2012-01-30 11:09:24.163', '00:00:03'),
(867, 13, 10, '2012-01-30 11:09:21.437', '2012-01-30 11:09:22.437', '00:00:01'),
(868, 13, 20, '2012-01-30 11:09:22.440', '2012-01-30 11:09:22.443', '00:00:00'),
(869, 13, 30, '2012-01-30 11:09:22.447', '2012-01-30 11:09:22.490', '00:00:00'),
(870, 13, 40, '2012-01-30 11:09:22.490', '2012-01-30 11:09:22.670', '00:00:00'),
(871, 13, 50, '2012-01-30 11:09:22.670', '2012-01-30 11:09:24.150', '00:00:02'),
(872, 13, 60, '2012-01-30 11:09:24.150', '2012-01-30 11:09:24.163', '00:00:00'),
(1038, 13, 0, '2012-01-30 23:12:51.240', '2012-01-30 23:12:54.837', '00:00:03'),
(1039, 13, 10, '2012-01-30 23:12:51.240', '2012-01-30 23:12:52.663', '00:00:01'),
(1040, 13, 20, '2012-01-30 23:12:52.667', '2012-01-30 23:12:52.680', '00:00:00'),
(1041, 13, 30, '2012-01-30 23:12:52.683', '2012-01-30 23:12:52.733', '00:00:00'),
(1042, 13, 40, '2012-01-30 23:12:52.733', '2012-01-30 23:12:53.003', '00:00:01'),
(1043, 13, 50, '2012-01-30 23:12:53.003', '2012-01-30 23:12:54.813', '00:00:01'),
(1044, 13, 60, '2012-01-30 23:12:54.817', '2012-01-30 23:12:54.833', '00:00:00'),
(1254, 13, 0, '2012-01-31 23:08:30.760', '2012-01-31 23:08:33.787', '00:00:03'),
(1255, 13, 10, '2012-01-31 23:08:30.763', '2012-01-31 23:08:31.733', '00:00:01'),
(1256, 13, 20, '2012-01-31 23:08:31.737', '2012-01-31 23:08:31.743', '00:00:00'),
(1257, 13, 30, '2012-01-31 23:08:31.743', '2012-01-31 23:08:31.800', '00:00:00'),
(1258, 13, 40, '2012-01-31 23:08:31.800', '2012-01-31 23:08:31.940', '00:00:00'),
(1259, 13, 50, '2012-01-31 23:08:31.943', '2012-01-31 23:08:33.627', '00:00:02'),
(1260, 13, 60, '2012-01-31 23:08:33.627', '2012-01-31 23:08:33.783', '00:00:00'),
(1470, 13, 0, '2012-02-02 04:03:14.497', '2012-02-02 04:03:17.323', '00:00:03'),
(1471, 13, 10, '2012-02-02 04:03:14.497', '2012-02-02 04:03:15.257', '00:00:01'),
(1472, 13, 20, '2012-02-02 04:03:15.257', '2012-02-02 04:03:15.263', '00:00:00'),
(1473, 13, 30, '2012-02-02 04:03:15.267', '2012-02-02 04:03:15.360', '00:00:00'),
(1474, 13, 40, '2012-02-02 04:03:15.360', '2012-02-02 04:03:15.637', '00:00:00'),
(1475, 13, 50, '2012-02-02 04:03:15.640', '2012-02-02 04:03:17.310', '00:00:02'),
(1476, 13, 60, '2012-02-02 04:03:17.310', '2012-02-02 04:03:17.320', '00:00:00'),
(1686, 13, 0, '2012-02-02 10:30:32.460', '2012-02-02 10:30:35.197', '00:00:03'),
(1687, 13, 10, '2012-02-02 10:30:32.463', '2012-02-02 10:30:33.487', '00:00:01'),
(1688, 13, 20, '2012-02-02 10:30:33.490', '2012-02-02 10:30:33.493', '00:00:00'),
(1689, 13, 30, '2012-02-02 10:30:33.497', '2012-02-02 10:30:33.517', '00:00:00'),
(1690, 13, 40, '2012-02-02 10:30:33.520', '2012-02-02 10:30:33.547', '00:00:00'),
(1691, 13, 50, '2012-02-02 10:30:33.550', '2012-02-02 10:30:35.180', '00:00:02'),
(1692, 13, 60, '2012-02-02 10:30:35.180', '2012-02-02 10:30:35.190', '00:00:00'),
(1858, 13, 0, '2012-02-02 22:20:36.840', '2012-02-02 22:20:39.997', '00:00:03'),
(1859, 13, 10, '2012-02-02 22:20:36.843', '2012-02-02 22:20:37.853', '00:00:01'),
(1860, 13, 20, '2012-02-02 22:20:37.857', '2012-02-02 22:20:37.863', '00:00:00'),
(1861, 13, 30, '2012-02-02 22:20:37.863', '2012-02-02 22:20:37.930', '00:00:00'),
(1862, 13, 40, '2012-02-02 22:20:37.933', '2012-02-02 22:20:38.317', '00:00:01'),
(1863, 13, 50, '2012-02-02 22:20:38.320', '2012-02-02 22:20:39.980', '00:00:01'),
(1864, 13, 60, '2012-02-02 22:20:39.983', '2012-02-02 22:20:39.993', '00:00:00'),
(2030, 13, 0, '2012-02-03 03:45:27.820', '2012-02-03 03:45:30.723', '00:00:03'),
(2031, 13, 10, '2012-02-03 03:45:27.820', '2012-02-03 03:45:28.880', '00:00:01'),
(2032, 13, 20, '2012-02-03 03:45:28.880', '2012-02-03 03:45:28.887', '00:00:00'),
(2033, 13, 30, '2012-02-03 03:45:28.887', '2012-02-03 03:45:28.923', '00:00:00'),
(2034, 13, 40, '2012-02-03 03:45:28.923', '2012-02-03 03:45:29.123', '00:00:01'),
(2035, 13, 50, '2012-02-03 03:45:29.127', '2012-02-03 03:45:30.707', '00:00:01'),
(2036, 13, 60, '2012-02-03 03:45:30.707', '2012-02-03 03:45:30.720', '00:00:00'),
(2246, 13, 0, '2012-02-06 10:25:28.507', '2012-02-06 10:25:31.693', '00:00:03'),
(2247, 13, 10, '2012-02-06 10:25:28.507', '2012-02-06 10:25:29.503', '00:00:01'),
(2248, 13, 20, '2012-02-06 10:25:29.503', '2012-02-06 10:25:29.523', '00:00:00'),
(2249, 13, 30, '2012-02-06 10:25:29.523', '2012-02-06 10:25:29.570', '00:00:00'),
(2250, 13, 40, '2012-02-06 10:25:29.570', '2012-02-06 10:25:29.810', '00:00:00'),
(2251, 13, 50, '2012-02-06 10:25:29.810', '2012-02-06 10:25:31.667', '00:00:02'),
(2252, 13, 60, '2012-02-06 10:25:31.667', '2012-02-06 10:25:31.690', '00:00:00'),
(2506, 13, 0, '2012-02-06 22:15:44.787', '2012-02-06 22:15:47.633', '00:00:03'),
(2507, 13, 10, '2012-02-06 22:15:44.787', '2012-02-06 22:15:45.913', '00:00:01'),
(2508, 13, 20, '2012-02-06 22:15:45.913', '2012-02-06 22:15:45.920', '00:00:00'),
(2509, 13, 30, '2012-02-06 22:15:45.923', '2012-02-06 22:15:45.967', '00:00:00'),
(2510, 13, 40, '2012-02-06 22:15:45.970', '2012-02-06 22:15:46.007', '00:00:01'),
(2511, 13, 50, '2012-02-06 22:15:46.007', '2012-02-06 22:15:47.610', '00:00:01'),
(2512, 13, 60, '2012-02-06 22:15:47.610', '2012-02-06 22:15:47.620', '00:00:00'),
(2678, 13, 0, '2012-02-07 03:44:24.393', '2012-02-07 03:44:27.510', '00:00:03'),
(2679, 13, 10, '2012-02-07 03:44:24.393', '2012-02-07 03:44:25.670', '00:00:01'),
(2680, 13, 20, '2012-02-07 03:44:25.673', '2012-02-07 03:44:25.680', '00:00:00'),
(2681, 13, 30, '2012-02-07 03:44:25.680', '2012-02-07 03:44:25.750', '00:00:00'),
(2682, 13, 40, '2012-02-07 03:44:25.753', '2012-02-07 03:44:25.990', '00:00:00'),
(2683, 13, 50, '2012-02-07 03:44:25.993', '2012-02-07 03:44:27.497', '00:00:02'),
(2684, 13, 60, '2012-02-07 03:44:27.500', '2012-02-07 03:44:27.510', '00:00:00'),
(2894, 13, 0, '2012-02-07 10:29:23.753', '2012-02-07 10:29:26.587', '00:00:03'),
(2895, 13, 10, '2012-02-07 10:29:23.753', '2012-02-07 10:29:24.700', '00:00:01'),
(2896, 13, 20, '2012-02-07 10:29:24.700', '2012-02-07 10:29:24.710', '00:00:00'),
(2897, 13, 30, '2012-02-07 10:29:24.710', '2012-02-07 10:29:24.733', '00:00:00'),
(2898, 13, 40, '2012-02-07 10:29:24.733', '2012-02-07 10:29:24.760', '00:00:00'),
(2899, 13, 50, '2012-02-07 10:29:24.760', '2012-02-07 10:29:26.540', '00:00:02'),
(2900, 13, 60, '2012-02-07 10:29:26.540', '2012-02-07 10:29:26.560', '00:00:00')

Select * From @ProcessHistory
原文

I have raw data that looks like this

enter image description here

I want to pivot it for a report to look like this

enter image description here

How do I pivot it like this in TSQL where the Starttimes is in order from left to right?

Here is the TSQL to generate the data

Declare @ProcessHistory Table ( Id Int, ProcessId Int, LocationId Int, StartTime DateTime, EndTime DateTime, Duration Time (0) )
Insert Into @ProcessHistory Values
(2, 13, 0, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.963', '00:00:00'),
(3, 13, 10, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.633', '00:00:00'),
(4, 13, 20, '2012-01-27 12:23:35.633', '2012-01-27 12:23:35.633', '00:00:00'),
(5, 13, 30, '2012-01-27 12:23:35.633', '2012-01-27 12:23:35.657', '00:00:00'),
(6, 13, 40, '2012-01-27 12:23:35.657', '2012-01-27 12:23:35.847', '00:00:00'),
(7, 13, 50, '2012-01-27 12:23:35.847', '2012-01-27 12:23:35.950', '00:00:00'),
(8, 13, 60, '2012-01-27 12:23:35.950', '2012-01-27 12:23:35.963', '00:00:00'),
(218, 13, 0, '2012-01-27 22:05:34.650', '2012-01-27 22:05:37.297', '00:00:03'),
(219, 13, 10, '2012-01-27 22:05:34.653', '2012-01-27 22:05:35.453', '00:00:01'),
(220, 13, 20, '2012-01-27 22:05:35.457', '2012-01-27 22:05:35.463', '00:00:00'),
(221, 13, 30, '2012-01-27 22:05:35.467', '2012-01-27 22:05:35.570', '00:00:00'),
(222, 13, 40, '2012-01-27 22:05:35.570', '2012-01-27 22:05:35.620', '00:00:00'),
(223, 13, 50, '2012-01-27 22:05:35.620', '2012-01-27 22:05:37.280', '00:00:02'),
(224, 13, 60, '2012-01-27 22:05:37.283', '2012-01-27 22:05:37.293', '00:00:00'),
(434, 13, 0, '2012-01-29 04:35:32.370', '2012-01-29 04:35:36.913', '00:00:04'),
(435, 13, 10, '2012-01-29 04:35:32.400', '2012-01-29 04:35:33.817', '00:00:01'),
(436, 13, 20, '2012-01-29 04:35:33.883', '2012-01-29 04:35:33.933', '00:00:00'),
(437, 13, 30, '2012-01-29 04:35:33.933', '2012-01-29 04:35:34.153', '00:00:01'),
(438, 13, 40, '2012-01-29 04:35:34.180', '2012-01-29 04:35:34.660', '00:00:00'),
(439, 13, 50, '2012-01-29 04:35:34.727', '2012-01-29 04:35:36.700', '00:00:02'),
(440, 13, 60, '2012-01-29 04:35:36.763', '2012-01-29 04:35:36.853', '00:00:00'),
(650, 13, 0, '2012-01-29 22:19:18.297', '2012-01-29 22:19:22.103', '00:00:04'),
(651, 13, 10, '2012-01-29 22:19:18.297', '2012-01-29 22:19:19.450', '00:00:01'),
(652, 13, 20, '2012-01-29 22:19:19.453', '2012-01-29 22:19:19.473', '00:00:00'),
(653, 13, 30, '2012-01-29 22:19:19.473', '2012-01-29 22:19:19.493', '00:00:00'),
(654, 13, 40, '2012-01-29 22:19:19.493', '2012-01-29 22:19:19.537', '00:00:00'),
(655, 13, 50, '2012-01-29 22:19:19.537', '2012-01-29 22:19:22.073', '00:00:03'),
(656, 13, 60, '2012-01-29 22:19:22.073', '2012-01-29 22:19:22.083', '00:00:00'),
(866, 13, 0, '2012-01-30 11:09:21.437', '2012-01-30 11:09:24.163', '00:00:03'),
(867, 13, 10, '2012-01-30 11:09:21.437', '2012-01-30 11:09:22.437', '00:00:01'),
(868, 13, 20, '2012-01-30 11:09:22.440', '2012-01-30 11:09:22.443', '00:00:00'),
(869, 13, 30, '2012-01-30 11:09:22.447', '2012-01-30 11:09:22.490', '00:00:00'),
(870, 13, 40, '2012-01-30 11:09:22.490', '2012-01-30 11:09:22.670', '00:00:00'),
(871, 13, 50, '2012-01-30 11:09:22.670', '2012-01-30 11:09:24.150', '00:00:02'),
(872, 13, 60, '2012-01-30 11:09:24.150', '2012-01-30 11:09:24.163', '00:00:00'),
(1038, 13, 0, '2012-01-30 23:12:51.240', '2012-01-30 23:12:54.837', '00:00:03'),
(1039, 13, 10, '2012-01-30 23:12:51.240', '2012-01-30 23:12:52.663', '00:00:01'),
(1040, 13, 20, '2012-01-30 23:12:52.667', '2012-01-30 23:12:52.680', '00:00:00'),
(1041, 13, 30, '2012-01-30 23:12:52.683', '2012-01-30 23:12:52.733', '00:00:00'),
(1042, 13, 40, '2012-01-30 23:12:52.733', '2012-01-30 23:12:53.003', '00:00:01'),
(1043, 13, 50, '2012-01-30 23:12:53.003', '2012-01-30 23:12:54.813', '00:00:01'),
(1044, 13, 60, '2012-01-30 23:12:54.817', '2012-01-30 23:12:54.833', '00:00:00'),
(1254, 13, 0, '2012-01-31 23:08:30.760', '2012-01-31 23:08:33.787', '00:00:03'),
(1255, 13, 10, '2012-01-31 23:08:30.763', '2012-01-31 23:08:31.733', '00:00:01'),
(1256, 13, 20, '2012-01-31 23:08:31.737', '2012-01-31 23:08:31.743', '00:00:00'),
(1257, 13, 30, '2012-01-31 23:08:31.743', '2012-01-31 23:08:31.800', '00:00:00'),
(1258, 13, 40, '2012-01-31 23:08:31.800', '2012-01-31 23:08:31.940', '00:00:00'),
(1259, 13, 50, '2012-01-31 23:08:31.943', '2012-01-31 23:08:33.627', '00:00:02'),
(1260, 13, 60, '2012-01-31 23:08:33.627', '2012-01-31 23:08:33.783', '00:00:00'),
(1470, 13, 0, '2012-02-02 04:03:14.497', '2012-02-02 04:03:17.323', '00:00:03'),
(1471, 13, 10, '2012-02-02 04:03:14.497', '2012-02-02 04:03:15.257', '00:00:01'),
(1472, 13, 20, '2012-02-02 04:03:15.257', '2012-02-02 04:03:15.263', '00:00:00'),
(1473, 13, 30, '2012-02-02 04:03:15.267', '2012-02-02 04:03:15.360', '00:00:00'),
(1474, 13, 40, '2012-02-02 04:03:15.360', '2012-02-02 04:03:15.637', '00:00:00'),
(1475, 13, 50, '2012-02-02 04:03:15.640', '2012-02-02 04:03:17.310', '00:00:02'),
(1476, 13, 60, '2012-02-02 04:03:17.310', '2012-02-02 04:03:17.320', '00:00:00'),
(1686, 13, 0, '2012-02-02 10:30:32.460', '2012-02-02 10:30:35.197', '00:00:03'),
(1687, 13, 10, '2012-02-02 10:30:32.463', '2012-02-02 10:30:33.487', '00:00:01'),
(1688, 13, 20, '2012-02-02 10:30:33.490', '2012-02-02 10:30:33.493', '00:00:00'),
(1689, 13, 30, '2012-02-02 10:30:33.497', '2012-02-02 10:30:33.517', '00:00:00'),
(1690, 13, 40, '2012-02-02 10:30:33.520', '2012-02-02 10:30:33.547', '00:00:00'),
(1691, 13, 50, '2012-02-02 10:30:33.550', '2012-02-02 10:30:35.180', '00:00:02'),
(1692, 13, 60, '2012-02-02 10:30:35.180', '2012-02-02 10:30:35.190', '00:00:00'),
(1858, 13, 0, '2012-02-02 22:20:36.840', '2012-02-02 22:20:39.997', '00:00:03'),
(1859, 13, 10, '2012-02-02 22:20:36.843', '2012-02-02 22:20:37.853', '00:00:01'),
(1860, 13, 20, '2012-02-02 22:20:37.857', '2012-02-02 22:20:37.863', '00:00:00'),
(1861, 13, 30, '2012-02-02 22:20:37.863', '2012-02-02 22:20:37.930', '00:00:00'),
(1862, 13, 40, '2012-02-02 22:20:37.933', '2012-02-02 22:20:38.317', '00:00:01'),
(1863, 13, 50, '2012-02-02 22:20:38.320', '2012-02-02 22:20:39.980', '00:00:01'),
(1864, 13, 60, '2012-02-02 22:20:39.983', '2012-02-02 22:20:39.993', '00:00:00'),
(2030, 13, 0, '2012-02-03 03:45:27.820', '2012-02-03 03:45:30.723', '00:00:03'),
(2031, 13, 10, '2012-02-03 03:45:27.820', '2012-02-03 03:45:28.880', '00:00:01'),
(2032, 13, 20, '2012-02-03 03:45:28.880', '2012-02-03 03:45:28.887', '00:00:00'),
(2033, 13, 30, '2012-02-03 03:45:28.887', '2012-02-03 03:45:28.923', '00:00:00'),
(2034, 13, 40, '2012-02-03 03:45:28.923', '2012-02-03 03:45:29.123', '00:00:01'),
(2035, 13, 50, '2012-02-03 03:45:29.127', '2012-02-03 03:45:30.707', '00:00:01'),
(2036, 13, 60, '2012-02-03 03:45:30.707', '2012-02-03 03:45:30.720', '00:00:00'),
(2246, 13, 0, '2012-02-06 10:25:28.507', '2012-02-06 10:25:31.693', '00:00:03'),
(2247, 13, 10, '2012-02-06 10:25:28.507', '2012-02-06 10:25:29.503', '00:00:01'),
(2248, 13, 20, '2012-02-06 10:25:29.503', '2012-02-06 10:25:29.523', '00:00:00'),
(2249, 13, 30, '2012-02-06 10:25:29.523', '2012-02-06 10:25:29.570', '00:00:00'),
(2250, 13, 40, '2012-02-06 10:25:29.570', '2012-02-06 10:25:29.810', '00:00:00'),
(2251, 13, 50, '2012-02-06 10:25:29.810', '2012-02-06 10:25:31.667', '00:00:02'),
(2252, 13, 60, '2012-02-06 10:25:31.667', '2012-02-06 10:25:31.690', '00:00:00'),
(2506, 13, 0, '2012-02-06 22:15:44.787', '2012-02-06 22:15:47.633', '00:00:03'),
(2507, 13, 10, '2012-02-06 22:15:44.787', '2012-02-06 22:15:45.913', '00:00:01'),
(2508, 13, 20, '2012-02-06 22:15:45.913', '2012-02-06 22:15:45.920', '00:00:00'),
(2509, 13, 30, '2012-02-06 22:15:45.923', '2012-02-06 22:15:45.967', '00:00:00'),
(2510, 13, 40, '2012-02-06 22:15:45.970', '2012-02-06 22:15:46.007', '00:00:01'),
(2511, 13, 50, '2012-02-06 22:15:46.007', '2012-02-06 22:15:47.610', '00:00:01'),
(2512, 13, 60, '2012-02-06 22:15:47.610', '2012-02-06 22:15:47.620', '00:00:00'),
(2678, 13, 0, '2012-02-07 03:44:24.393', '2012-02-07 03:44:27.510', '00:00:03'),
(2679, 13, 10, '2012-02-07 03:44:24.393', '2012-02-07 03:44:25.670', '00:00:01'),
(2680, 13, 20, '2012-02-07 03:44:25.673', '2012-02-07 03:44:25.680', '00:00:00'),
(2681, 13, 30, '2012-02-07 03:44:25.680', '2012-02-07 03:44:25.750', '00:00:00'),
(2682, 13, 40, '2012-02-07 03:44:25.753', '2012-02-07 03:44:25.990', '00:00:00'),
(2683, 13, 50, '2012-02-07 03:44:25.993', '2012-02-07 03:44:27.497', '00:00:02'),
(2684, 13, 60, '2012-02-07 03:44:27.500', '2012-02-07 03:44:27.510', '00:00:00'),
(2894, 13, 0, '2012-02-07 10:29:23.753', '2012-02-07 10:29:26.587', '00:00:03'),
(2895, 13, 10, '2012-02-07 10:29:23.753', '2012-02-07 10:29:24.700', '00:00:01'),
(2896, 13, 20, '2012-02-07 10:29:24.700', '2012-02-07 10:29:24.710', '00:00:00'),
(2897, 13, 30, '2012-02-07 10:29:24.710', '2012-02-07 10:29:24.733', '00:00:00'),
(2898, 13, 40, '2012-02-07 10:29:24.733', '2012-02-07 10:29:24.760', '00:00:00'),
(2899, 13, 50, '2012-02-07 10:29:24.760', '2012-02-07 10:29:26.540', '00:00:02'),
(2900, 13, 60, '2012-02-07 10:29:26.540', '2012-02-07 10:29:26.560', '00:00:00')

Select * From @ProcessHistory

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

慈悲佛祖 2025-01-09 22:42:11

关于原始问题中的评论：

最简单的方法是在 Excel 中进行数据透视。 SQL Server 将要求您指定透视操作中的每一列，您必须动态执行此操作。 （这太糟糕了！）

如果您可以将如上所示的结果放入 Excel 工作表中，然后让第二个工作表（在同一工作簿中）读取第一个工作表并执行透视，那么您就成功了。

In regards to the comments in the original question:

The easiest way to do this is going to be doing the pivot in Excel. SQL Server will require that you specify every single column in the pivot operation, which you'll have to do dynamically. (Which is awful!)

If you can get the results as shown above into an Excel sheet, then have a second sheet (in the same workbook) read from the first and perform the pivot, you're golden.

回复 原文
巨坚强 2025-01-09 22:42:11

正如 @Norla 所说，这在 SQL 中是相当令人讨厌的，但是下面的内容应该可以帮助你实现这一点（顺便说一句，完全未经测试，所以可能有一些小鬼）：

首先，你需要构建一个列名列表（其中是 starttime 列中的唯一值：

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + startTime+ ']',
                         '[' + startTime+ ']')
FROM    ProcessHistory
ORDER BY startTime

然后您需要将此字符串提供给动态 SQL 查询：

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT processId,locationId, '+@cols +'
               FROM
               (SELECT  t.processId,
                        t.locationId,
                        t.starttime,
                        t.duration
               FROM    ProcessHistory) t PIVOT ( SUM(duration)
                                                 FOR starttime IN ( '+@cols +' )
                                               ) AS pvt'

然后使用以下命令执行它：

EXECUTE(@query)

As @Norla said, this is pretty nasty to do in SQL, but the below should get you there (totally untested, by the way, so may have a few gremlins):

First, you need to construct a list of column names (which is the unique values in your starttime column:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + startTime+ ']',
                         '[' + startTime+ ']')
FROM    ProcessHistory
ORDER BY startTime

Then you need to feed this string into a dynamic SQL query:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT processId,locationId, '+@cols +'
               FROM
               (SELECT  t.processId,
                        t.locationId,
                        t.starttime,
                        t.duration
               FROM    ProcessHistory) t PIVOT ( SUM(duration)
                                                 FOR starttime IN ( '+@cols +' )
                                               ) AS pvt'

and then execute it with:

EXECUTE(@query)
回复 原文
寻找我们的幸福 2025-01-09 22:42:11

好吧，你的问题有很多需要考虑的地方。您需要动态 SQL，因此您确实应该首先查看此链接。另外，您的 Duration 列的数据类型为 TIME，因此不容易进行 SUM，有很多 CAST 和 CONVERT 来完成。一旦我这么说，我尝试了这个查询并且工作得很好（看看你的预期结果）：

DECLARE @Dates NVARCHAR(MAX)='', @Q NVARCHAR(MAX), @FormatedDates NVARCHAR(MAX) = ''

SELECT  @Dates = @Dates + '[' + CONVERT(VARCHAR(16),StartTime,120) + '],',
        @FormatedDates = @FormatedDates + 'CONVERT(TIME(0),CAST([' + CONVERT(VARCHAR(16),StartTime,120) +'] AS DATETIME)) AS [' + CONVERT(VARCHAR(16),StartTime,120) +'],'
FROM ProcessHistory
GROUP BY CONVERT(VARCHAR(16),StartTime,120)

SELECT  @Dates = LEFT(@Dates,LEN(@Dates)-1),
        @FormatedDates = LEFT(@FormatedDates,LEN(@FormatedDates)-1)

SET @Q = '
SELECT ProcessId, LocationId, '+@FormatedDates+'
FROM (  SELECT  ProcessId, LocationId, CONVERT(VARCHAR(16),StartTime,120) StartTime, 
                CAST(CONVERT(DATETIME,Duration) AS FLOAT) Duration
        FROM ProcessHistory) A
PIVOT (SUM(Duration) FOR StartTime IN ('+@Dates+')) PT'


EXEC sp_executesql @Q

Ok, there is a lot to take into account with your question. You need dynamic SQL, so you really should take a look at this link first. Also, your Duration column is of data type TIME, so its not easy to SUM, there is a lot of CAST and CONVERT to do. Once that I said that, I tried this query and worked fine (looking at your expected results):

DECLARE @Dates NVARCHAR(MAX)='', @Q NVARCHAR(MAX), @FormatedDates NVARCHAR(MAX) = ''

SELECT  @Dates = @Dates + '[' + CONVERT(VARCHAR(16),StartTime,120) + '],',
        @FormatedDates = @FormatedDates + 'CONVERT(TIME(0),CAST([' + CONVERT(VARCHAR(16),StartTime,120) +'] AS DATETIME)) AS [' + CONVERT(VARCHAR(16),StartTime,120) +'],'
FROM ProcessHistory
GROUP BY CONVERT(VARCHAR(16),StartTime,120)

SELECT  @Dates = LEFT(@Dates,LEN(@Dates)-1),
        @FormatedDates = LEFT(@FormatedDates,LEN(@FormatedDates)-1)

SET @Q = '
SELECT ProcessId, LocationId, '+@FormatedDates+'
FROM (  SELECT  ProcessId, LocationId, CONVERT(VARCHAR(16),StartTime,120) StartTime, 
                CAST(CONVERT(DATETIME,Duration) AS FLOAT) Duration
        FROM ProcessHistory) A
PIVOT (SUM(Duration) FOR StartTime IN ('+@Dates+')) PT'


EXEC sp_executesql @Q
回复 原文
久而酒知 2025-01-09 22:42:11

也许是这样的：

测试数据：

CREATE TABLE ProcessHistory ( Id Int, ProcessId Int, LocationId Int, StartTime DateTime, EndTime DateTime, Duration Time (0) )
Insert Into ProcessHistory Values
(2, 13, 0, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.963', '00:00:00'),
..............................

获取唯一的列：

DECLARE @cols VARCHAR(MAX)
;WITH CTE AS
(
    SELECT 
        convert(varchar, tbl.StartTime, 101)+' '+LEFT(convert(varchar, tbl.StartTime, 114),5) AS StartTime
    FROM 
        ProcessHistory AS tbl
), 
CTE2 AS
(
    SELECT
        ROW_Number() OVER(PARTITION BY CTE.StartTime ORDER BY CTE.StartTime DESC) AS RowNbr,
        CTE.StartTime
    FROM
        CTE
)
SELECT
    @cols = COALESCE(@cols + ','+QUOTENAME(StartTime),
                 QUOTENAME(StartTime))
FROM 
    CTE2
WHERE
    CTE2.RowNbr=1

执行动态sql（我选择以秒为单位计算持续时间的总和）：

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl.ProcessId,
        tbl.LocationId,
        DATEDIFF(s, 0, tbl.Duration) AS Duration,
        convert(varchar, tbl.StartTime, 101)+'' ''+LEFT(convert(varchar, tbl.StartTime, 114),5) AS StartTime
    FROM
        ProcessHistory AS tbl
) AS P
PIVOT
(
    SUM(Duration)
    FOR StartTime IN('+@cols+')
) AS Pvt'

EXECUTE(@query)

然后在我的例子中，我将删除表：

DROP TABLE ProcessHistory

Maybe something like this:

The test data:

CREATE TABLE ProcessHistory ( Id Int, ProcessId Int, LocationId Int, StartTime DateTime, EndTime DateTime, Duration Time (0) )
Insert Into ProcessHistory Values
(2, 13, 0, '2012-01-27 12:23:35.373', '2012-01-27 12:23:35.963', '00:00:00'),
..............................

Getting the unique columns:

DECLARE @cols VARCHAR(MAX)
;WITH CTE AS
(
    SELECT 
        convert(varchar, tbl.StartTime, 101)+' '+LEFT(convert(varchar, tbl.StartTime, 114),5) AS StartTime
    FROM 
        ProcessHistory AS tbl
), 
CTE2 AS
(
    SELECT
        ROW_Number() OVER(PARTITION BY CTE.StartTime ORDER BY CTE.StartTime DESC) AS RowNbr,
        CTE.StartTime
    FROM
        CTE
)
SELECT
    @cols = COALESCE(@cols + ','+QUOTENAME(StartTime),
                 QUOTENAME(StartTime))
FROM 
    CTE2
WHERE
    CTE2.RowNbr=1

Executing the dynamic sql (I have chosen to sum the duration in seconds):

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl.ProcessId,
        tbl.LocationId,
        DATEDIFF(s, 0, tbl.Duration) AS Duration,
        convert(varchar, tbl.StartTime, 101)+'' ''+LEFT(convert(varchar, tbl.StartTime, 114),5) AS StartTime
    FROM
        ProcessHistory AS tbl
) AS P
PIVOT
(
    SUM(Duration)
    FOR StartTime IN('+@cols+')
) AS Pvt'

EXECUTE(@query)

Then in my case I'am dropping the table:

DROP TABLE ProcessHistory
回复 原文
~没有更多了~

关于作者

轻拂→两袖风尘

暂无简介

文章
评论
28 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

5040234068

文章 0 评论 0

樱花雨梦

文章 0 评论 0

≈。彩虹

文章 0 评论 0

雨轻弹

文章 0 评论 0

血之狂魔

文章 0 评论 0

qq_0bIjwE

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文