T-SQL Pivot – How to Pivot Without Fixed Columns?

pivotsql servert-sql

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

Best Answer

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
Related Question