Sql-server – MS SQL Server cycle calculation

This is a great example of how the Window Functions can be used. Based on your data, I'm assuming you want to do this for every unique LINE_NO. I'll leave it to you to format the percentage column.

SELECT      LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY,
            COUNT(*) AS CT,
            COUNT(*) / SUM(COUNT(*) * 1.0) OVER (PARTITION BY LINE_NO)

FROM        TX_HSA_SUMM

GROUP BY    LINE_NO,
            E_FIELD,
            F_FIELD,
            G_FIELD,
            HSA_STATUS,
            FAMILY,
            CACHE_FAMILY

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!