How to Pivot Multiple Columns in SQL Server

You are on the right track with your current query but I would alter it slightly to use a subquery and include a row_number() to give a distinct value to each score in your rank.

If you query your data using:

select [rank], score,
  row_number() over(partition by [rank] order by [rank]) rn
from yourtable

See Demo

You will get a sequenced number for each score inside your ranks. This sequence number will then be grouped by in your PIVOT making your full code:

select [1], [2], [3]
from
(
  select [rank], score,
    row_number() over(partition by [rank] order by [rank]) rn
  from yourtable
) d
pivot
(
  max(score)
  for [rank] in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. This query gives you a result:

| 1 |   2 | 3 |
|---|-----|---|
| 1 | 2.5 | 5 |
| 7 | 2.5 | 8 |
| 9 | 2.5 | 5 |

Prior to writing a dynamic SQL query, you should always write a version that is hard-coded so you can get the syntax correct. So the first thing you need to do is write a working PIVOT query for any of the TruckId values that you need.

Static Version:

Let's say you need TruckID = 3, your code for the PIVOT would be similar to the following:

select Name, TruckId, [1], [2], [3], [4], [5], [6]
from
(
  select Name, TruckId, Compartment, Capacity
  from Trucks t
  inner join Compartments c
    on t.Id = c.TruckId
  where t.Id = 3  -- your truck id here
) d
pivot
(
  max(capacity)
  for compartment in ([1], [2], [3], [4], [5], [6])  -- your Compartment values here
) p;

See SQL Fiddle with Demo

Dynamic Version:

Now, your problem is that you are going to have a variety of Compartment values for any of your TruckIds so you need to write something that will accept any TruckId and produce the results that you want. The PIVOT function will work but first you'll need to concatenate a SQL string with all the Compartment values for each TruckId.

First, you'll define all your parameters:

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @ParmDefinition NVARCHAR(500),
  @TruckId as int;

set @TruckId = 3;  -- this would be the value you submit via your SP
set @ParmDefinition = '@id int';

Next, you'll concatenate the list of the new column headers, using FOR XML PATH and STUFF:

select @cols = STUFF((SELECT ',' + QUOTENAME(Compartment) 
                    from #Compartments
                    where TruckId = @TruckId
                    group by id, Compartment
                    order by Id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

Once you have the list of columns, you'll create the full sql string that will be executed. This should be similar to the static version above:

set @query 
    = N'SELECT Name, TruckId, ' + @cols + N' 
        from 
        (
            select t.Name, c.TruckId, c.Compartment, c.Capacity
            from #Trucks t
            inner join #Compartments c
              on t.Id = c.TruckId
            where t.Id = @id
        ) x
        pivot 
        (
            max(Capacity)
            for Compartment in (' + @cols + N')
        ) p ';

The last step is executing the sql string:

exec sp_executesql @query, @ParmDefinition, @id = @TruckId;

See a demo. This gives a result of:

Name    TruckId 1   2   3   4   5   6   7   8   9   10  
------- ------- --- --- --- --- --- --- --- --- --- --- 
BBB-WWW 3       500 500 500 500 500 500 500 500 500 500