--

Shipments

CREATE TABLE Shipments
(
	 [Shipment Number]	BIGINT
	,[DB Source] VARCHAR(10)
	,[Weight] BIGINT
)
GO

INSERT INTO Shipments VALUES
(24584,'MGXT',35000	 ),
(24969,'MGXT',100	 ),
(26341,'MGXT',15000	 )
GO

[Billing Info]

CREATE TABLE [Billing Info]
(
	 [Shipment Number]	BIGINT	
	,[Charge Type] VARCHAR(10)
	,[Description] VARCHAR(25)
	,[Billing Line] INT
	,[Billing Amount] INT
)
GO

INSERT INTO [Billing Info] VALUES
(24584,'HNDL','Handling			',1,	200			),
(24584,'SVC'	,'Service Fee			',2,	20	),
(24969,'MISC','Miscellaneous Charge',1,	50			),
(26341,'TRAN','Transportation		',1,	365		),
(26341,'PUC'	,'Pickup Charge		',2,	25		),
(26341,'LTX'	,'Local Tax			',3,	5.5		)
GO

[Invoice Table]

CREATE TABLE [Invoice Table]		
(
	 [Shipment Number]	BIGINT	
	,[Invoice Number] VARCHAR(10)
	,[Invoice Date] DATETIME
	,[Invoice Currency] VARCHAR(3)
)
GO

INSERT INTO [Invoice Table] VALUES
(24584,'50012A','2017/05/08','USD'),
(24969,'50489A','2017/06/09','USD'),
(26341,'50773A','2017/07/10','USD')
GO

--

SELECT Type,[Shipment Number],[Invoice Number],[Invoice Date],[InvoiceWeight],[DB Source]
FROM 
(
	SELECT 'Header' Type , CAST(s.[Shipment Number] AS sql_variant) [Shipment Number] ,CAST(I.[Invoice Number] AS sql_variant) [Invoice Number]
		,CAST(I.[Invoice Date] AS sql_variant) [Invoice Date] ,CAST(s.[Weight] AS sql_variant) [InvoiceWeight]
		,s.[DB Source]  ,s.[Shipment Number] Ord , 1 Ord1
	FROM Shipments s
	FULL JOIN [Invoice Table] I ON I.[Shipment Number] = s.[Shipment Number]

	UNION ALL

	SELECT Type , u.[Invoice Currency],t.[Billing Amount] [InvoiceAmount], t.[Charge Type],t.[Description]
		, '' [DB Source], u.[Shipment Number] Ord  , 2 Ord1
	FROM 
	(
		SELECT s.*,I.[Invoice Currency],I.[Invoice Date],I.[Invoice Number]
		FROM Shipments s
		FULL JOIN [Invoice Table] I ON I.[Shipment Number] = s.[Shipment Number]
	)u
	CROSS APPLY
	(
		SELECT 'Charges' Type , b.* 
		FROM [Billing Info] b 
		WHERE b.[Shipment Number] = u.[Shipment Number]
	)t
)k
ORDER BY Ord,Ord1

--