SQL Server select records from header and body table
I don't see why this won't work:
IF OBJECT_ID(N'dbo.Header', N'U') IS NOT NULL
DROP TABLE dbo.Header;
IF OBJECT_ID(N'dbo.Body', N'U') IS NOT NULL
DROP TABLE dbo.Body;
GO
CREATE TABLE dbo.Header
(
    iGroup int
    , sName varchar(50)
    , iValue int
);
CREATE TABLE Body
(
    iGroup int
    , sName varchar(50)
    , iValue int
);

INSERT INTO dbo.Header (iGroup, sName, iValue)
VALUES (1, 'test1', 10)
    , (2, 'test2', 20);

INSERT INTO dbo.Body (iGroup, sName, iValue)
VALUES (1, 'test3', 30)
    , (2, 'test4', 40);


;WITH src AS 
(
    SELECT iGroup
        , sName
        , iValue
        , 0 as sort_by
    FROM Header
    UNION ALL 
    SELECT iGroup
        , sName
        , iValue
        , 1 as sort_by
    FROM Body
)
SELECT Id = ROW_NUMBER() OVER (ORDER BY sort_by, iGroup, sName)
    , src.*
FROM src
ORDER BY sort_by, iGroup, sName;

Results:
╔════╦════════╦═══════╦════════╗
║ Id ║ iGroup ║ sName ║ iValue ║
╠════╬════════╬═══════╬════════╣
║  1 ║      1 ║ test1 ║     10 ║
║  2 ║      1 ║ test3 ║     30 ║
║  3 ║      2 ║ test2 ║     20 ║
║  4 ║      2 ║ test4 ║     40 ║
╚════╩════════╩═══════╩════════╝

Also, don't use reserved keywords as column names, please!