SQL Server using unpivot with a new table

Question

I am not really a programmer so my knowledge about programming generally and SQL specifically is less than anybody on here. I am not sure if this question is easy or not but I really not sure how to get started. So, I have this table called Photonics:

I want to change it so that the result will be displayed in a new table like this:

Name        Timestamp   Value
Photonics   1/1/12 0:15 239.04
Photonics   1/1/12 0:30 247.68
Photonics   1/1/12 0:45 253.44
Photonics   1/1/12 1:00 254.88
Photonics   1/1/12 1:15 253.44
Photonics   1/1/12 1:30 239.04
Photonics   1/1/12 1:45 239.04
Photonics   1/1/12 2:00 239.04
Photonics   1/1/12 2:15 239.04
Photonics   1/1/12 2:30 250.56

Somebody told me this is called Pivot! and I found that topic too complicated for a beginner like me.

Answer 1

The process of converting columns of data into rows is known as an UNPIVOT. There are several ways that you can UNPIVOT the data:

You can use a UNION ALL query:

select 'Photonics' name, dt + cast('12:15 AM' as datetime) timestamp, [12:15 AM] as value
from Photonics
union all
select 'Photonics' name, dt + cast('12:30 AM' as datetime) timestamp, [12:30 AM] as value
from Photonics
union all
select 'Photonics' name, dt + cast('12:45 AM' as datetime) timestamp, [12:45 AM] as value
from Photonics;

See SQL Fiddle with Demo.

You can use the UNPIVOT function in SQL Server 2005+:

select 'Photonics' name,
  timestamp = dt + cast(timestamp as datetime),
  value
from photonics p
unpivot
(
  value
  for timestamp in ([12:15 AM], [12:30 AM], [12:45 AM], [1:00 AM], [1:15 AM])
) unpiv;

See SQL Fiddle with Demo.

You can use CROSS APPLY with Values in SQL Server 2008+:

select 'Photonics' name,
  timestamp = dt + cast(timestamp as datetime),
  value
from photonics p
cross apply
(
  values
  ('12:15 AM', [12:15 AM]),
  ('12:30 AM', [12:30 AM]),
  ('12:45 AM', [12:45 AM]), 
  ('1:00 AM', [1:00 AM]),
  ('1:15 AM', [1:15 AM])
) c (timestamp, value);

See SQL Fiddle with Demo