How to pivot without fixed columns in TSQL?

Question

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](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [TruckId] [int] NOT NULL,
 [Compartment] [nvarchar](50) 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].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (11, 3, N'1', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (12, 3, N'2', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (13, 3, N'3', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (14, 3, N'4', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (15, 3, N'5', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (16, 3, N'6', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (17, 3, N'7', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (18, 3, N'8', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (19, 3, N'9', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (20, 3, N'10', 500)
SET IDENTITY_INSERT [dbo].[Compartments] OFF 

I want to write a SP that returns a list of compartments for a certain Truck (based on the TruckId). This is the easy part:

SELECT * FROM COMPARTMENTS WHERE TruckId = @p_TruckId

This gives me a table that looks like this:

Results of query

What I want to do, is return a table that has the compartment name as header and the capacity as value.

This will be a table with only 1 row of values. The number of columns depends of the number of compartments for that given truck.

I looked into the PIVOT function, but you need to know the amount of columns in advance for that. I also found a dynamic PIVOT example, but I can't seem to make it work in my situation.

Can somebody assist me here? That would be great!

EDIT: A link to the example I've found

• Taryn: I also found a dynamic PIVOT example, but I can't seem to make it work in my situation Do you have any of the code you tried to get PIVOT working?
• Mr. T.: Not exactly. It is all rubbish. I don't understand what I'm supposed to change :-/. I will update the post with the example I've found.
• Taryn: It doesn't look like Compartment has set values, meaning that TruckId = 1 has completely different values than TruckId = 2, etc, typically when you want to pivot the column names are usually the same or similar across different ids, this report is going to have a lot of null values because the column headers are all different. What exactly are you trying to do here? It doesn't sound like pivot is what you need. Here's an example of what it would look like if you pivot - sqlfiddle.com/#!3/f24e5/2 and why I'm not sure it's what you want.
• Mr. T.: I will only need the compartments for a particular truck. So if I pass TruckId = 3, I wish to see 10 columns with the capacity in the first row. When I pass TruckId = 2, I wish to see 4 columns, ...
• Mr. T.: If it is easier to just give numbers to the headers in stead of the name, that is fine with me. An ORDER BY Compartment ASC and then put the rownumber as header for example.

Answer 1

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 

• Mr. T.: This seems very nice (more code than expected, but hey, if it does the job, it does the job! Perfect!
• Dai: FWIW, SQL Server 2017 supports STRING_AGG which eliminates the need for the STUFF/FOR XML hack.