title: SQL Server pivot query alternative or optimize
body: <p>So I have these tables:</p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>I want a query that will combine data from both table like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I know I can do this with a pivot like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>