Converting table date

9 Dec 2005 7:52 PM

Jim Abel

I am trying to to take data from a table where the Row data contains 24
columns representing hors 12 midnight to 11 PM see (sample below) into a
table that puts 1 hour per row?
Initial table
Name     Date       12Min 1Min    2Min 3Min â€¦ 11Min
Mickey 12/2/05     625.4    153.2 84635â€¦

Convbert to another table

Name    Date                      Min
Mickey 12/2/05 00:00:00 625.4
Mickey 12/2/05 01:00:00 153.2
Mickey   12/2/05 02:00:00 84635

9 Dec 2005 8:40 PM

Trey Walpole

Use unions.

e.g. (note: while you don't need to dateadd(hour,0,[Date]), but this
will work if the datatype is datetime or varchar)

select Name, dateadd(hour, 0, [Date]) as [Date], 12Min as Min from yourtable
union all
select Name, dateadd(hour, 1, [Date]) as [Date], 1Min as Min from yourtable
union all
....
union all
select Name, dateadd(hour, 11, [Date]) as [Date], 11Min as Min from
yourtable

Jim Abel wrote:
Show quote

> I am trying to to take data from a table where the Row data contains 24
> columns representing hors 12 midnight to 11 PM see (sample below) into a
> table that puts 1 hour per row?
> Initial table
> Name     Date       12Min 1Min    2Min 3Min â€¦ 11Min
> Mickey 12/2/05     625.4    153.2 84635â€¦
>
> Convbert to another table
>
> Name    Date                      Min
> Mickey 12/2/05 00:00:00 625.4
> Mickey 12/2/05 01:00:00 153.2
> Mickey   12/2/05 02:00:00 84635
>