SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table StudentMarkList(
  StudentID int  , 
  StudentName varchar(100), 
  Performance varchar(100), 
  class varchar(100),  
  Section varchar(100),   
  subject1 varchar(100), 
  subjectmark1 varchar(100),   
  subject2 varchar(100),  
  subjectmark2 varchar(100),  
  subject3 varchar(100),  
  subjectmark3 varchar(100), 
  subject4 varchar(100),   
  subjectmark4 varchar(100),  
  subject5 varchar(100),  
  subjectmark5 varchar(100),  
  subject6 varchar(100),  
  subjectmark6 varchar(100));
insert into StudentMarkList values(1, 'shiva', 'not bad','10th','C','science',58,'social','','english',70,'maths','','biology',67,'',58)
1 rows affected

select * from StudentMarkList;


StudentID
StudentName
Performance
class
Section
subject1
subjectmark1
subject2
subjectmark2
subject3
subjectmark3
subject4
subjectmark4
subject5
subjectmark5
subject6
subjectmark6


1
shiva
not bad
10th
C
science
58
social

english
70
maths

biology
67

58


SELECT   Studentid, att, att_val--, Val
FROM (   
  SELECT StudentID,StudentName,Performance,class,Section,
    subject1    ,subjectmark1,subject2,subjectmark2,subject3,subjectmark3,
    subject4,subjectmark4,subject5,subjectmark5,subject6,subjectmark6
  FROM StudentMarkList
) sml
UNPIVOT
(   att_val FOR att IN 
      (StudentName,Performance,class,subject1,subject2,subject3,subject4,subject5,subject6)
)   AS VT_Transposee;


Studentid
att
att_val


1
StudentName
shiva

1
Performance
not bad

1
class
10th

1
subject1
science

1
subject2
social

1
subject3
english

1
subject4
maths

1
subject5
biology

1
subject6



SELECT   Studentid, att, [att_val    val]
FROM (   
  SELECT StudentID,
          convert(nvarchar(max),StudentName) StudentName,
          convert(nvarchar(max),Performance)Performance,
          convert(nvarchar(max),class) class,
          convert(nvarchar(max),Section) Section,
          convert(nvarchar(max),concat(left(concat(subject1,'          '),10),':',subjectmark1)) s1,
	  convert(nvarchar(max),concat(left(concat(subject2,'          '),10),':',subjectmark2)) s2,
	  convert(nvarchar(max),concat(left(concat(subject3,'          '),10),':',subjectmark3)) s3,
	  convert(nvarchar(max),concat(left(concat(subject4,'          '),10),':',subjectmark4)) s4,
	  convert(nvarchar(max),concat(left(concat(subject5,'          '),10),':',subjectmark5)) s5,
	  convert(nvarchar(max),concat(left(concat(subject6,'          '),10),':',subjectmark6)) s6
  FROM StudentMarkList
) sml
UNPIVOT
(   [att_val    val] FOR att IN 
      (StudentName,Performance,class,
	  s1,s2,s3,s4,s5,s6)
)   AS VT_Transposee;


Studentid
att
att_val    val


1
StudentName
shiva

1
Performance
not bad

1
class
10th

1
s1
science   :58

1
s2
social    :

1
s3
english   :70

1
s4
maths     :

1
s5
biology   :67

1
s6
          :58
Studentid	att	att_val
1	StudentName	shiva
1	Performance	not bad
1	class	10th
1	subject1	science
1	subject2	social
1	subject3	english
1	subject4	maths
1	subject5	biology
1	subject6