Select con cabecera y detalle

[iislas]

Veamos si esto da solucion a tu consulta

Código SQL:

CREATE TABLE Header
(
      InvoiceNumber INT
    , [DATE] DATETIME 
    , Amount DECIMAL(10,2)
)
GO
 
INSERT INTO Header VALUES
(100,GETDATE(),100.00),
(101,GETDATE()+2,100.00)
GO
 
CREATE TABLE Details
(
     InvoiceNumber INT
    ,Customer VARCHAR(100)
    ,Purchaseorder VARCHAR(100)
    ,DetailAmount DECIMAL(30,2)
)
GO
 
INSERT INTO Details VALUES
(100,'Customer A', 'Purchase Order A', 1000.2 ),
(100,'Customer B', 'Purchase Order B', 787.23 ),
(101,'Customer C', 'Purchase Order F', 12131.99),
(101,'Customer D', 'Purchase Order G', 2334.18)
GO
 
SELECT InvoiceNumber , [DATE] , Amount
FROM 
(
    SELECT CAST(InvoiceNumber AS sql_variant) InvoiceNumber , CAST( [DATE]  AS sql_variant) [DATE] , Amount 
    , CONVERT(FLOAT,InvoiceNumber) rnk 
    FROM Header
    UNION ALL
    SELECT Customer, purchaseorder, detailamount , rnk FROM (
    SELECT id.*, concat(id.InvoiceNumber, '.', ROW_NUMBER() OVER (PARTITION BY id.InvoiceNumber ORDER BY id.invoicenumber)) rnk
    FROM Details i 
    INNER JOIN Details id ON i.InvoiceNumber = id.InvoiceNumber) K 
)p ORDER BY rnk
 

InvoiceNumber Date Amount
100 2017-09-06 09:53:53.270 100.00
Customer A Purchase Order A 1000.20
Customer B Purchase Order B 787.23
Customer A Purchase Order A 1000.20
Customer B Purchase Order B 787.23
101 2017-09-08 09:53:53.270 100.00
Customer C Purchase Order F 12131.99
Customer D Purchase Order G 2334.18
Customer C Purchase Order F 12131.99
Customer D Purchase Order G 2334.18