Using Union query to combine multiple queries into single in SQL Server

Hi makumbi,

Please refer below sample.

SQL

CREATE TABLE #Archivedreports
( 
   [Name] VARCHAR (50),
   [Engscore] VARCHAR (50),
   [Engrade] VARCHAR (50),
   [Mathsscore] VARCHAR (50),
   [Mathsgrade] VARCHAR (50),
   [Sciencescore] VARCHAR (50),
   [Sciencegrade] VARCHAR (50),
   [Type] VARCHAR (50),
   [Monthe] VARCHAR (50)
)
INSERT INTO #Archivedreports VALUES ('ABC','52','A','40','A+','52','B+','1','Jan')
INSERT INTO #Archivedreports VALUES ('ABC','45','B','47','B+','87','A+','2','Feb')
INSERT INTO #Archivedreports VALUES ('ABC','85','C','52','C+','45','C+','3','Mar')
INSERT INTO #Archivedreports VALUES ('ABC','25','D','75','A+','78','A+','4','Apr')

SELECT  TOP (100) PERCENT Name, engscore 'Score', engrade 'Grade', 'ENG' AS Subject, Type, Monthe
FROM #Archivedreports
WHERE (Type IS NOT NULL)
UNION 
SELECT TOP (100) PERCENT Name, mathsscore, mathsgrade, 'MATHS' AS Subject, Type, Monthe
FROM #Archivedreports
WHERE (Type IS NOT NULL)
UNION 
SELECT TOP (100) PERCENT Name, sciencescore, sciencegrade, 'SCIE' AS Subject, Type, Monthe
FROM #Archivedreports
WHERE (Type IS NOT NULL)
UNION  
SELECT TOP (100) PERCENT Name, sciencescore, sciencegrade, 'SST' AS Subject, Type, Monthe
FROM #Archivedreports
WHERE (Type IS NOT NULL)
ORDER BY Type

OutPut