Alex Alex - 2 months ago 13
SQL Question

SQL Server pivot query alternative or optimize

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

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
Comments