Join three selects into one

I’m using SQL Server 13.0.5102.14.

I have these three selects:

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

select cod.Serial as ItemNO,
       varData.Value as Lot,
       pro.ProductCode as Product
  from dbo.Code cod,
       dbo.VariableData varData,
       dbo.Product pro,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 1 and
       varData.ProductionOrderId = @prodID and
       varData.VariableDataId = @lotAI and
       proOrd.ProductionOrderId = @prodID and
       pro.ProductId = proOrd.ProductId

select cod.Serial as Box
  from dbo.Code cod,
       dbo.AggregationChildren aggCh,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 2 and
       cod.CodeId = aggCh.AggregationChildrenId

select cod.Serial as Pallet
  from dbo.Code cod,
       dbo.Aggregation agg,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 3 and
       cod.CodeId = agg.AggregationId

I want to join these three selects into one to get this:

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

My problem here is that I’m using Code table to get three different values.

How can I do it to join these three selects into one?

Answer

Dirty using union and assuming your current queries work.

select cod.Serial,' as ItemNO'
  from dbo.Code cod,
       dbo.VariableData varData,
       dbo.Product pro,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 1 and
       varData.ProductionOrderId = @prodID and
       varData.VariableDataId = @lotAI and
       proOrd.ProductionOrderId = @prodID and
       pro.ProductId = proOrd.ProductId
union
select cod.Serial,' as Box'
  from dbo.Code cod,
       dbo.AggregationChildren aggCh,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 2 and
       cod.CodeId = aggCh.AggregationChildrenId
union      
Select varData.Value,' as Lot'
  from dbo.Code cod,
       dbo.VariableData varData,
       dbo.Product pro,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 1 and
       varData.ProductionOrderId = @prodID and
       varData.VariableDataId = @lotAI and
       proOrd.ProductionOrderId = @prodID and
       pro.ProductId = proOrd.ProductId
union
select cod.Serial,' as Pallet'
  from dbo.Code cod,
       dbo.Aggregation agg,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 3 and
       cod.CodeId = agg.AggregationId
union      
select pro.ProductCode,' as Product'
  from dbo.Code cod,
       dbo.VariableData varData,
       dbo.Product pro,
       dbo.ProductionOrder proOrd
 where cod.ProductionOrderId = @prodID and
       cod.AggregationLevel = 1 and
       varData.ProductionOrderId = @prodID and
       varData.VariableDataId = @lotAI and
       proOrd.ProductionOrderId = @prodID and
       pro.ProductId = proOrd.ProductId

You could improve this question by providing sample data and expected output