Add two tables in sql

Since Table B is dynamic, and there are possibilities for dynamic column with varying column name, there can be challenge in specifically mention the column name as B.Test1, B.Test2 as there can be Test3, Test4, etc unless dynamic query is applied.

Result using dynamic query (with sample records):

BEGIN TRAN

DECLARE @TestColumns NVARCHAR(MAX)
SET @TestColumns = N'B.TEST1,B.TEST2';

CREATE TABLE A (Id INT,  Name VARCHAR(100),    Mark INT, Attendence INT, Total INT);

INSERT INTO A VALUES (1,'Aswathi',30,20,50);
INSERT INTO A VALUES (2,'Benny',20,30,50);

CREATE TABLE B (Name VARCHAR(100),  Test1 INT, Test2 INT);

INSERT INTO B VALUES('Aswathi',20,40)
INSERT INTO B VALUES('Benny',10,30)

DECLARE @QuerySQL NVARCHAR(MAX)
SET @QuerySQL = N'
SELECT
    A.ID, A.Name, ' + @TestColumns + ', A.Mark, A.Attendence, A.Total
    FROM A
INNER JOIN B
    ON A.Name = B.Name'


EXECUTE(@QuerySQL)

ROLLBACK TRAN

Suggest, instead of creating dynamic column in respect with number of Test, each Test and corresponding marks can be recorded as each Table record like,

CREATE TABLE B (Name VARCHAR(100),  Test VARCHAR(25), Mark INT);
INSERT INTO B VALUES('Aswathi','Test1',20)
INSERT INTO B VALUES('Aswathi','Test2',40)

INSERT INTO B VALUES('Benny','Test1',10)
INSERT INTO B VALUES('Benny','Test2',30)

Using this structure, result can be achieved using the query below.

BEGIN TRAN
CREATE TABLE A (Id INT,  Name VARCHAR(100),    Mark INT, Attendence INT, Total INT);

INSERT INTO A VALUES (1,'Aswathi',30,30,0);
INSERT INTO A VALUES (2,'Benny',20,30,0);


CREATE TABLE B (Name VARCHAR(100),  Test VARCHAR(25), Mark INT);
INSERT INTO B VALUES('Aswathi','Test1',20)
INSERT INTO B VALUES('Aswathi','Test2',40)

INSERT INTO B VALUES('Benny','Test1',10)
INSERT INTO B VALUES('Benny','Test2',30)

SELECT Name, AVG(Mark) Mark INTO #tmp FROM B GROUP BY Name

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
 COALESCE(
  @PivotColumnHeaders + ',[' + [Test] + ']',
  '[' + [Test] + ']'
 )
FROM (SELECT DISTINCT TEST FROM B) TMP
ORDER BY Test

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT A.Id,TMP1.*,TMP2.Mark,A.Attendence,TMP2.Mark + A.Attendence Total FROM
(SELECT * FROM b
    PIVOT (
        AVG(Mark)
        FOR Test IN (' + @PivotColumnHeaders + ')) AS PivotTable
) TMP1
INNER JOIN #TMP TMP2
    ON TMP1.Name = TMP2.Name
INNER JOIN A
    ON A.Name = TMP1.Name
'
EXECUTE(@PivotTableSQL)


ROLLBACK TRAN

Note: You may avoid DISTINCT clause by refer to any Test master if available.
Mark is calculated using AVG. Based on your requirement this can be changed to SUM.

Why can't we just do like this?

SELECT A.Id, B.*, A.Mark, A.Attendence, A.Total
FROM dbo.A
INNER JOIN dbo.B ON A.Name = B.Name