CREATE TABLE #Trucks( [Id] [int] IDENTITY(1,1) NOT NULL, ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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

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

CREATE TABLE #Compartments(
    [Id] [int]  IDENTITY(1,1) NOT NULL,
    [TruckId] [int] NOT NULL,
    [Compartment] [nvarchar](50) NOT NULL,
    [Capacity] [bigint] NULL)
SET IDENTITY_INSERT #Compartments ON 
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (1, 1, N'C1', 5000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (2, 1, N'C2', 4000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (3, 1, N'C3', 5000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (4, 1, N'C4', 4000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (5, 1, N'C5', 6000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (6, 2, N'Vak 1', 6000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (8, 2, N'Vak 2', 6000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (9, 2, N'Vak 3', 5000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (10, 2, N'Vak 4', 5000)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (11, 3, N'1', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (12, 3, N'2', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (13, 3, N'3', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (14, 3, N'4', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (15, 3, N'5', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (16, 3, N'6', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (17, 3, N'7', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (18, 3, N'8', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (19, 3, N'9', 500)
INSERT INTO #Compartments ([Id], [TruckId], [Compartment], [Capacity]) VALUES (20, 3, N'10', 500)
SET IDENTITY_INSERT #Compartments OFF;

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @ParmDefinition NVARCHAR(500),
  @TruckId as int;
  
set @TruckId = 3;
SET @ParmDefinition = '@id int';

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,'');

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 '

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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results