How to unpivot a table in SQL Server

Example 1

Starting from the #Students temporary table

CREATE TABLE #Students(
[Name] varchar(50),
[Surname] varchar(50),
[Period] int,
[Sport] int,
[History] int,
[English] int,
[Geography] int);

INSERT INTO
#Students([Name],[Surname],[Period],[Sport],[History],[English],[Geography])
VALUES
('Luke','Green',1,30,20,23,NULL),
('Mary','Brown',1,17,15,NULL,30),
('John','Red',1,18,NULL,21,30),
('Walter','White',1,22,20,5,30),
('John','Red',2,NULL,NULL,NULL,23);;

you can unpivot the date of period 1 of subject/mark with the following query.

SELECT Name, Surname, Sbj, Mark
FROM #Students
UNPIVOT
(
  Mark
  for Sbj in ([Sport],[History],[English],[Geography])
) u
WHERE Period=1

You can find an interactive version of this example following this link .