Hi nadeem1218,
Refer below SQL Query
CREATE TABLE #Table1(EmpId Int,Name VARCHAR(50),[Total-Marks] INT)
INSERT INTO #Table1 VALUES(100,'Test',500)
INSERT INTO #Table1 VALUES(101,'Test1',420)
INSERT INTO #Table1 VALUES(102,'Test2',500)
CREATE TABLE #Table2(EmpId Int,[Subject] VARCHAR(50),Marks INT)
INSERT INTO #Table2 VALUES(100,'English',80)
INSERT INTO #Table2 VALUES(100,'Maths',92)
INSERT INTO #Table2 VALUES(101,'Science',75)
INSERT INTO #Table2 VALUES(101,'Algebra',92)
INSERT INTO #Table2 VALUES(102,'English',80)
INSERT INTO #Table2 VALUES(102,'Maths',92)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')+ QUOTENAME([Subject])
FROM (SELECT DISTINCT [Subject] FROM #Table2) AS [Subjects]
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT *
FROM (
SELECT t.EmpId,t.Name,t.[Total-marks],t2.Marks,t2.[Subject]
FROM #Table1 t
FULL OUTER JOIN #Table2 t2
ON t.EmpId = t2.EmpId
) p
PIVOT(Max(Marks) FOR [Subject] IN (' +@ColumnName+ ')) AS pvt'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
DROP TABLE #Table1
DROP TABLE #Table2
Output
EmpId |
Name |
Total-marks |
Algebra |
English |
Maths |
Science |
100 |
Test |
500 |
NULL |
80 |
92 |
NULL |
101 |
Test1 |
420 |
92 |
NULL |
NULL |
75 |
102 |
Test2 |
500 |
NULL |
80 |
92 |
NULL |