--

CREATE TABLE TABLE_A
(
  Id   VARCHAR(9),
  Data  VARCHAR(10)
)
GO

CREATE TABLE TABLE_B
(
  Id   VARCHAR(9),
  Data  VARCHAR(10)
)
GO

CREATE TABLE TABLE_C
(
  Id   VARCHAR(9),
  Data  VARCHAR(10)
)
GO

---DATA

Insert into TABLE_A (Id, Data)
Values ('P-4404', 'F350.0');
Insert into TABLE_A  (Id, Data)
Values ('P-0600', 'F350.0');
Insert into TABLE_A (Id, Data)
Values ('P-0603', 'F350.0');
Insert into TABLE_A  (Id, Data)
Values ('P-6171', 'F350.0');
Insert into TABLE_A  (Id, Data)
Values ('P-6533', 'F350.0');

----TABLE B
Insert into TABLE_B
   (Id, Data)
Values
   ('P-4404', 'F353.0');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-4404', 'F353.1');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-0600', 'F353.1');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-0600', 'F353.0');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-5283', 'F353.1');
Insert into TABLE_B
   (Id, Data)
Values
   ('P-0603', 'F353.0');

---TABLE C

Insert into TABLE_C
   (Id, Data)
Values
   ('P-4404', 'F555.5');
Insert into TABLE_C
   (Id, Data)
Values
   ('P-9022', 'F555.5');
Insert into TABLE_C
   (Id, Data)
Values
   ('P-2613', 'F555.5');


--

SELECT  
	Id
    ,ISNULL(MAX(CASE WHEN Data = 'F350.0' THEN 'Y' END),'') [F350_0]
    ,ISNULL(MAX(CASE WHEN Data = 'F353.0' THEN 'Y' END),'') [F353_0]
    ,ISNULL(MAX(CASE WHEN Data = 'F353.1' THEN 'Y' END),'') [F353_1]
    ,ISNULL(MAX(CASE WHEN Data = 'F555.5' THEN 'Y' END),'') [F555_5]
FROM 
(
    select a.Id,a.Data FROM TABLE_A a 
    UNION ALL
    SELECT b.Id,b.Data FROM TABLE_B b
    UNION ALL
    SELECT c.Id,c.Data FROM TABLE_C c
)a
GROUP BY Id

--

--

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

--