declare @t table (Location varchar(20), Variable varchar(20), Col1 varchar(20), Col2 varchar(20), Col3 varchar(20), Col4 varchar(20), Col5 varchar(20))
insert @t
          select 'London', 'DateTime', '10Feb3AM', '10Feb9AM', '10Feb3PM', '10Feb9PM', '11Feb3AM'
union all select 'London', 'Temperature', '10', '11', '12', '12', '8'
union all select 'London', 'Weather', 'FA', 'FA', 'SH', 'FA', 'FA'
union all select 'London', 'MaxTemp', '11', '13', '14', '14', '11'
union all select 'London', 'MinTemp', '7', '10', '10', '8', '5'

select id,
    max(case when Variable = 'DateTime' then Col else '' end) as 'DateTime',
    max(case when Variable = 'Temperature' then Col else '' end) as 'Temperature',
    max(case when Variable = 'Weather' then Col else '' end) as 'Weather',
    max(case when Variable = 'MaxTemp' then Col else '' end) as 'MaxTemp',
    max(case when Variable = 'MinTemp' then Col else '' end) as 'MinTemp'
from (
              select 1 as id, Location, Variable, Col1 as Col from @t
    union all select 2, Location, Variable, Col2 from @t
    union all select 3, Location, Variable, Col3 from @t
    union all select 4, Location, Variable, Col4 from @t
    union all select 5, Location, Variable, Col5 from @t) a
group by id