SQL - Insert one column to another, without merging data

Question

Searching the threads I could only find combining or merging, but not inserting and keeping original values. So here is what I have:

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

What I need is:

While ignoring Nulls. Thanks!

Just a tip, in case you want to play with this later, add a column to the second table if you can, "timesource", populate with the source column — JohnHC, Apr 21 '17 at 10:08
possible duplicate of:http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all — lalithkumar, Apr 21 '17 at 10:10
@lalithkumar, sorry, I did not know about the UNION command. that is why I did not detect it. — Johnny, Apr 21 '17 at 10:14

score 4 · Answer 1 · answered Apr 21 '17 at 10:07

4

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

answered Apr 21 '17 at 10:07

juergen d

201,996
37
293
362

For some reason it only gives me the time1 values, I'm currently trying to understand what's wrong because it's probably my fault – Johnny Apr 21 '17 at 10:18
I didn't think it matters but I have two id's. so each id can have multiple rows depending on the second id. for now I only get the time1 column I have no idea why.. – Johnny Apr 21 '17 at 10:28

ANR Upgraded Version · Accepted Answer · 2017-04-21T10:28:43.390

1

Try this with unpivot where it handles by default NULL value comparison and excludes those from result set.

Reference purpose a temp table along with the sample data given in thread

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)

Solution is here with the above temp table data

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

edited Apr 21 '17 at 10:28

answered Apr 21 '17 at 10:23

ANR Upgraded Version

857
16
39

Thanks! this one works great except I need also to remove duplicates, any idea how to do that? or should I just remove them with different command – Johnny Apr 21 '17 at 10:41
I also would like to keep track on where the value came from (Time1,time2,time3) – Johnny Apr 21 '17 at 10:41
SELECT ID, [time],[timecolumnName] FROM #TempTable UNPIVOT ( [time] for [timecolumnName] in (time1, time2,time3) ) unpiv; this will enable you tracking the column name – ANR Upgraded Version Apr 21 '17 at 11:16

score 0 · Answer 3 · answered Apr 21 '17 at 10:07

Do you only have the three columns? If so just do;

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;

If you may have more than three let us know - you can still do this with Dynamic SQL

SQL - Insert one column to another, without merging data

3 Answers3