将三个查询合二为一

我正在使用 SQL Server 13.0.5102.14。

我有这三个表：

CREATE TABLE [dbo].[ProductionOrder]
(
    [ProductionOrderId] INT NOT NULL IDENTITY (1, 1), 
    [ProductId] INT NOT NULL, 
    [Name] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_ProductionOrder] PRIMARY KEY CLUSTERED 
    (
        [ProductionOrderId] ASC
    ))
)

CREATE TABLE [dbo].[Code]
(
    [CodeId] int NOT NULL IDENTITY(1, 1),
    [Serial] [varchar](38) not null,
    [ProductionOrderId] int NOT NULL,
    [AggregationLevel] [tinyint] NOT NULL,
     CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED 
    (
        [CodeId] ASC
    ),
    CONSTRAINT [UC_Code_Serial] UNIQUE ([Serial]),
    CONSTRAINT [FK_Code_ProductionOrder_ProductionOrderId] FOREIGN KEY ([ProductionOrderId]) REFERENCES [dbo].[ProductionOrder] ([ProductionOrderId]))
)

CREATE TABLE [dbo].[VariableData]
(
    [ProductionOrderId] INT NOT NULL,
    [AggregationLevelConfigurationId] TINYINT NOT NULL, 
    [VariableDataId] VARCHAR(4) NOT NULL, 
    [Value] NVARCHAR(200) NOT NULL,
    CONSTRAINT [PK_VariableData] PRIMARY KEY CLUSTERED 
    (
        [AggregationLevelConfigurationId] ASC,
        [ProductionOrderId] ASC,
        [VariableDataId] ASC
    ), 
    CONSTRAINT [FK_VariableData_AggregationLevelConfiguration_AggregationLevelConfigurationId] FOREIGN KEY ([AggregationLevelConfigurationId], [ProductionOrderId]) REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId], [ProductionOrderId]) ON DELETE CASCADE,
    CONSTRAINT [FK_VariableData_ProductionOrder_ProductionOrderId] FOREIGN KEY ([ProductionOrderId]) REFERENCES [dbo].[ProductionOrder] ([ProductionOrderId]),
    CONSTRAINT [CK_VariableData_VariableDataId] CHECK (([VariableDataId]<>N''))
)

CREATE TABLE [dbo].[Product]
(
    [ProductId] INT NOT NULL IDENTITY (1, 1),
    [ProductCode] VARCHAR(14) not null,
    [Description] NVARCHAR(50) NULL,
    [LawId] TINYINT NOT NULL,  
    [Name] NVARCHAR(100) NOT NULL, 
    [Comment] NVARCHAR(100) NULL, 
    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
    (
        [ProductId] ASC
    ),
    CONSTRAINT [CK_Product_ProductCode] CHECK (([ProductCode]<>N'')),
    CONSTRAINT [CK_Product_Name] CHECK (([Name]<>N''))
)

CREATE TABLE [dbo].[AggregationChildren]
(
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId] INT NOT NULL,
    [Position] [int] NOT NULL,
    CONSTRAINT [PK_AggregationChildren] PRIMARY KEY CLUSTERED 
    (
        [AggregationChildrenId] ASC
    ), 
    CONSTRAINT [FK_AggregationChildren_Aggregation_AggregationId] FOREIGN KEY ([AggregationId]) REFERENCES [Aggregation]([AggregationId]) ON DELETE CASCADE, 
    CONSTRAINT [FK_AggregationChildren_Code_AggregationChildrenId] FOREIGN KEY ([AggregationChildrenId]) REFERENCES [Code]([CodeId])
)

CREATE TABLE [dbo].[Aggregation]
(
    [AggregationId] INT NOT NULL,
    [Created] varchar(34) NULL,
    CONSTRAINT [PK_Aggregation] PRIMARY KEY CLUSTERED
    (
        [AggregationId] ASC
    ),
    CONSTRAINT [FK_Aggregation_Code_AggregationId] FOREIGN KEY ([AggregationId]) REFERENCES [dbo].[Code] ([CodeId])
)

我有这三个工作查询：

declare @prodID int = 1;
declare @lotAI varchar(4) = '10';

select cod.Serial as ItemNO
     , varData.Value as Lot
     , pro.ProductCode as Product

  from dbo.VariableData varData 
        JOIN dbo.Code cod ON varData.ProductionOrderId = cod.ProductionOrderId
        JOIN dbo.ProductionOrder proOrd ON varData.ProductionOrderId = proOrd.ProductionOrderId
        JOIN dbo.Product pro on proOrd.ProductId = pro.ProductId   

 where varData.ProductionOrderId = @prodID and
       varData.VariableDataId = @lotAI and
       varData.AggregationLevelConfigurationId = 1 and
       cod.AggregationLevel = 1


select cod.Serial as Box

  from dbo.Code cod
       LEFT JOIN dbo.AggregationChildren agg on agg.AggregationId = cod.CodeId

 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 2


select cod.Serial as Pallet

  from dbo.Code cod
       JOIN dbo.Aggregation agg on agg.AggregationId = cod.CodeId

 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 3

我想将所有这些加入到一个查询中。我的问题是我dbo.Code用来获得三个不同的值：

• select cod.Serial as ItemNO
• select cod.Serial as Box
• select cod.Serial as Pallet

如何将这三个查询合二为一？