How to pivot without fixed columns in TSQL?

I'm struggling with pivoting a quite simple table. All examples and tutorials on the web, are not what I'm looking for, so maybe you guys can help me out here (I must say that my T-SQL knowledge isn't so good...)

Let me explain the situation:

I have a table Trucks

CREATE TABLE [dbo].[Trucks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL) 

SET IDENTITY_INSERT [dbo].[Trucks] ON 
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (1, N'AAA-BBB')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (2, N'AAA-CCC')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (3, N'BBB-WWW')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (4, N'SKL-POL')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (5, N'QAS-ZSD')
SET IDENTITY_INSERT [dbo].[Trucks] OFF

Each truck has an amount of compartments. Each compartment has a name and a capacity

```
CREATE TABLE [dbo].Compartments NOT NULL,
[TruckId] [int] NOT NULL,
[Compartment] nvarchar NOT NULL,
[Capacity] [bigint] NULL)
SET IDENTITY_INSERT [dbo].[Compartments] ON
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (1, 1, N'C1', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (2, 1, N'C2', 4000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (3, 1, N'C3', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (4, 1, N'C4', 4000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (5, 1, N'C5', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (6, 2, N'Vak 1', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (8, 2, N'Vak 2', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (9, 2, N'Vak 3', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (10, 2, N'Vak 4', 5000)
INSERT [dbo].[Compar

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

StackExchange Database Administrators Q#119844, answer score: 14