SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test(	
	[recordid] int IDENTITY(1,1) PRIMARY KEY,
	[name] [nvarchar](25) NULL,
	[testcentre] [nvarchar](25) NULL,
	[coursename] [nvarchar](25) NULL,
	[testtype] [nvarchar](25) NULL,
	[testscore] [int],
	[testdate] [smalldatetime] NOT NULL,
	[retestflag] [int],
	[Preferred_Output] [int],
	[RejectReason] [nvarchar](75) NULL
)
INSERT INTO test
           (
	[name],
	[testcentre],
	[coursename],
	[testtype],
	[testscore],
	[testdate],
	[retestflag],
	[Preferred_Output],
	[RejectReason]  )
	VALUES
('Sam', 'Paris', 'English', 'IELTS', 90, '01/02/2019',0,0,''),
('Sam', 'Paris', 'English', 'IELTS', 95, '02/02/2019',1,1,'Better score in retest'),
('Sam', 'Paris', 'English', 'TOEFL', 80, '04/02/2019',0,0,'Within 4 days of previous test'),
('Jack', 'London', 'English', 'IELTS', 90, '01/02/2019',0,1,''),
('Jack', 'London', 'English', 'IELTS', 90, '02/02/2019',1,0,'Same or bad score in retest'),
('Louis', 'Brazil', 'English', 'IELTS', 70, '01/02/2019',0,1,''),
('Louis', 'Brazil', 'English', 'IELTS', 70, '02/02/2019',1,0,'Same score in retest'),
('Louis', 'Brazil', 'English', 'TOEFL', 100, '04/02/2019',0,0,'Within 4 days of previous test')

8 rows affected

select * from test


recordid
name
testcentre
coursename
testtype
testscore
testdate
retestflag
Preferred_Output
RejectReason


1
Sam
Paris
English
IELTS
90
2019-01-02 00:00
0
0


2
Sam
Paris
English
IELTS
95
2019-02-02 00:00
1
1
Better score in retest

3
Sam
Paris
English
TOEFL
80
2019-04-02 00:00
0
0
Within 4 days of previous test

4
Jack
London
English
IELTS
90
2019-01-02 00:00
0
1


5
Jack
London
English
IELTS
90
2019-02-02 00:00
1
0
Same or bad score in retest

6
Louis
Brazil
English
IELTS
70
2019-01-02 00:00
0
1


7
Louis
Brazil
English
IELTS
70
2019-02-02 00:00
1
0
Same score in retest

8
Louis
Brazil
English
TOEFL
100
2019-04-02 00:00
0
0
Within 4 days of previous test

recordid	name	testcentre	coursename	testtype	testscore	testdate	retestflag	Preferred_Output	RejectReason
1	Sam	Paris	English	IELTS	90	2019-01-02 00:00	0	0
2	Sam	Paris	English	IELTS	95	2019-02-02 00:00	1	1	Better score in retest
3	Sam	Paris	English	TOEFL	80	2019-04-02 00:00	0	0	Within 4 days of previous test
4	Jack	London	English	IELTS	90	2019-01-02 00:00	0	1
5	Jack	London	English	IELTS	90	2019-02-02 00:00	1	0	Same or bad score in retest
6	Louis	Brazil	English	IELTS	70	2019-01-02 00:00	0	1
7	Louis	Brazil	English	IELTS	70	2019-02-02 00:00	1	0	Same score in retest
8	Louis	Brazil	English	TOEFL	100	2019-04-02 00:00	0	0	Within 4 days of previous test