Transpose rows into columns in SQL Server 2008 R2

Question

How do I turn this:

and this:

into this:

in SQL Server 2008 R2?

Answer 1

This question is very similar to this one PIVOT rows to columns with more than 1 value returned, where you need to aggregate string data from rows into columns. I will modify that answer to demonstrate how you can convert your data to your final result.

Since you are aggregating string values, you will need to apply either the min() or max() aggregate function, but in order to get the final result to display more than one row you need something to force the multiple rows.

In order to do this you will want to use row_number() to generate a unique sequence number for each parameter in the name. When you apply the PIVOT function, this number will be used in the grouping and you will generate multiple rows:

select Car, Truck, Bicycle
from
(
  select vt.name, vp.parameter,
    row_number() over(partition by vt.name
                      order by vt.id) seq
  from vehicle_types vt
  left join vehicle_parameters vp
    on vt.id = vp.vehicletype
) d
pivot
(
  max(parameter)
  for name in (Car, Truck, Bicycle)
) piv;

See SQL Fiddle with Demo.

This could also be written using an aggregate function with a CASE expression:

select 
  max(case when name = 'Car' then parameter end) Car,
  max(case when name = 'Truck' then parameter end) Truck,
  max(case when name = 'Bicycle' then parameter end) Bicycle
from
(
  select vt.name, vp.parameter,
    row_number() over(partition by vt.name
                      order by vt.id) seq
  from vehicle_types vt
  left join vehicle_parameters vp
    on vt.id = vp.vehicletype
) d
group by seq;

See SQL Fiddle with Demo.

The above two versions are great if you have a known number of names, then you can hard-code the query but if you don't then you will have to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(name) 
                    from vehicle_types
                    group by id, name
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
             from 
             (
                select vt.name, vp.parameter,
                  row_number() over(partition by vt.name
                                    order by vt.id) seq
                from vehicle_types vt
                left join vehicle_parameters vp
                  on vt.id = vp.vehicletype
            ) x
            pivot 
            (
                max(parameter)
                for name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. All versions give a result:

|    CAR |            TRUCK |    BICYCLE |
|--------|------------------|------------|
|   make |          maxload |      frame |
|   year | hasconcretemixer |     isroad |
| engine |           (null) | ismountain |