Creating a table from an exixting table in different pattern?

Question

I'm using SQL Server. I have a table in my database which is like

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 

There are more rows like above.

But I would like to create another table which will just use those id and put time# in separate column against respective id like this:

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

Can anyone please give me a head start? Or even a solution would be highly appreciated.

Answer 1

You could just run four separate queries to get the desired results (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

Or, you could do the following which would keep duplicates from being inserted (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

Answer 2

That the definition of UNPIVOT. If you're using SQLServer 2005 or better you can use

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 demo

UNPIVOT require that all the columns that are unpivoted have the same type and dimension