SQL-在不合并数据的情况下将一列插入另一列

Question

搜索我只能找到组合或合并的线程，而不是插入和保持原始值。所以这就是我所拥有的：

ID time1 time2 time3
1   20    NULL  30
2   50    NULL  NULL
2   20    30    40

我需要的是：

ID time
1   20
1   30
2   50
2   20
2   30
2   40

忽略零。谢谢！

Answer 1

select id, time1 as time from your_table where time1 is not null
union all 
select id, time2 from your_table where time2 is not null
union all 
select id, time3 from your_table where time3 is not null

Answer 2

尝试使用undivot尝试一下，默认情况下处理 NULL值比较并将结果排除在结果集中。

参考目的临时表以及线程中给出的示例数据

CREATE TABLE #TempTable(
 ID int,
 time1 int, time2 int, time3 int)
 INSERT INTO #TempTable (ID, time1, time2,time3)
  VALUES (1,   20,    NULL,  30), 
  (2,   50,    NULL,  NULL), 
  (2,   20,    30,    40)

解决方案与上述临时表数据

  SELECT ID, [time]
  FROM #TempTable
  UNPIVOT
 (
  [time]
 for [timecolumnName] in (time1, time2,time3)
 ) unpiv;

Answer 3

您只有三列吗？如果是这样，只是;

INSERT [NewTable]
(Id, time)
SELECT Id, time1
FROM [OldTable]
WHERE time1 IS NOT NULL;
INSERT [NewTable]
(Id, time)
SELECT Id, time2
FROM [OldTable]
WHERE time2 IS NOT NULL;
INSERT [NewTable]
(Id, time)
SELECT Id, time3
FROM [OldTable]
WHERE time3 IS NOT NULL;

如果您可能有三个以上的让我们知道 - 您仍然可以使用Dynamic SQL