在不同模式下从现有表创建表格？

Question

我正在使用SQL Server。我在我的数据库中有一个类似于以下的表：

id    stops    time1    time2    time3    time4
-----------------------------------------------
1    Exit1    6:00 AM  8:00 AM  10:00 AM  12:00 PM
2    Exit2    7:00 AM  9:00 AM  11:00 AM  1:00 PM 

以上还有更多类似的行。

但我想创建另一个表，它将仅使用那些 id，并在相应的 id 对应的列中放置 time#。

id    times
------------
1    6:00 AM
1    8:00 AM
1    10:00 AM
1    12:00 PM
2    7:00 AM
2    9:00 AM

有人可以给我一个起点吗？甚至一个解决方案也将不胜感激。

- saz

---

对于id = 2 ，11:00 AM 1:00 PM 怎么处理？ - Grijesh Chauhan

@GrijeshChauhan 我只是举了一个例子。还会有更多类似的行。 - saz

MySQL中没有PIVOT命令，所以我认为@Linger的答案是一个好建议。 - Grijesh Chauhan

Answer 1

您可以运行四个单独的查询来获取所需结果（SQL Fiddle）：

INSERT INTO MyTable
(id, times)
SELECT id, time1
FROM MyOtherTable

INSERT INTO MyTable
(id, times)
SELECT id, time2
FROM MyOtherTable

INSERT INTO MyTable
(id, times)
SELECT id, time3
FROM MyOtherTable

INSERT INTO MyTable
(id, times)
SELECT id, time4
FROM MyOtherTable

或者，你可以按照以下步骤来避免插入重复数据(SQL Fiddle):

INSERT INTO MyTable
(id, times)
SELECT id, time1
FROM MyOtherTable
UNION 
SELECT id, time2
FROM MyOtherTable
UNION
SELECT id, time3
FROM MyOtherTable
UNION
SELECT id, time4
FROM MyOtherTable

- Linger

---

谢谢@Linger。你的第二个解决方案现在运行良好。我唯一遇到的问题是，它正在按升序填充新表的时间列。我猜测列的数据格式正在做这件事。因为没有一个时间列实际上是时间数据格式。这里有什么关于数据格式的技巧吗？ - saz

它插入记录的方式并不重要。当您查询表格以获取信息时，可以指定排序方式。但是，如果您的数据类型是varchar，例如，它将无法正确排序。也就是说，不会按时间顺序排序。相反，它将按数字字母顺序排序，如此示例所示。 - Linger

当前数据格式为varchar，所以按数字顺序排序。实际上，我需要按时间排序。 - saz

你可以在 ORDER BY 中使用 CONVERT() 函数，就像以下的 SQL Fiddle 示例一样。 - Linger

Answer 2

如果您使用SQLServer 2005或更高版本，可以使用UNPIVOT的定义。

INSERT INTO OtherTable
SELECT id, [time]
FROM   (SELECT id, time1, time2, time3, time4 FROM Table1) t
       UNPIVOT
       ([time] FOR times IN (time1, time2, time3, time4)) pvt

SQLFiddle演示

UNPIVOT要求所有被拆分的列具有相同的类型和维度。

- Serpiton

---

我想现在采用Linger的解决方案。我从未使用过UNPIVOT，如果这不起作用，我稍后会尝试它。谢谢@Serpiton - saz