SQL Server pivot query alternative or optimize

Question

So I have these tables:

-- tbl_obs
id  lat  lon   created
-------------------------
1   1.2  -2.1  2002-08-03
2   1.9  -5.5  2002-08-03
3   1.5  -4.1  2002-08-03

-- tbl_obsdata
id  name         value     obs_id
---------------------------------
1   gender       Male       1
2   type         Type I     1
3   description  Some desc  1
4   gender       Female     2
5   type         Type II    2
6   description  Some desc  2
7   gender       Female     3
8   type         Type II    3
9   description  Some desc  3

I want a query that will combine data from both table like this:

lat  lon   created     gender  type  description
------------------------------------------------
1.2  -2.1  2002-08-03  Male   Type I  Some desc
1.9  -5.5  2002-08-03  Female Type I  Some desc
1.5  -4.1  2002-08-03  Male   Type II Some desc

I know I can do this with a pivot like:

with cte as (
 select obsdata.name, obsdata.value, obs.lat, obs.lon, obs.created
 from obsdata
 left join obs on obs.id = obsdata.obs_id
)
select lat, lon, created, gender, type, description
from cte
pivot(
 max(value)
 for [name] in (gender, type, description)
) as pvt

So far this returns the result (I think), but I have about a million rows and this runs really slow. Any alternative way to achieve this that would be much faster? I'm using SQL Server 2012.

Answer 1

Another option is

Select A.lat
      ,A.lon
      ,A.created
      ,gender      = max(IIF(B.name='gender',B.value,null))
      ,type        = max(IIF(B.name='type',B.value,null))
      ,description = max(IIF(B.name='description',B.value,null))
 From  tbl_obs A
 Join  tbl_obsdata B on (A.id=B.obs_id)
 Group By A.lat
      ,A.lon
      ,A.created

Returns

lat lon     created     gender  type    description
1.2 -2.1    2002-08-03  Male    Type I  Some desc
1.5 -4.1    2002-08-03  Female  Type II Some desc
1.9 -5.5    2002-08-03  Female  Type II Some desc