SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Exam (
  STD VARCHAR(10),
  SUBJECT VARCHAR(50),
  MARKS VARCHAR(10),
  COMMENT VARCHAR(50),
  TDATE DATE
);

INSERT INTO Exam (STD, SUBJECT, MARKS, COMMENT, TDATE)
VALUES
  ('ST1', 'MATH', '25%', 'POOR', '2021-02-01'),
  ('ST1', 'ENGLISH', '88%', 'DIST', '2021-02-02'),
  ('ST1', 'SCIENCE', '56%', 'PASS', '2021-02-04');
3 rows affected

SELECT STD, concat(COLUMN_NAME,rnk) as COLUMN_NAME, VALUE
FROM
(
    SELECT STD, 
           CAST(SUBJECT AS sql_variant) AS SUBJECT, 
           CAST(MARKS AS sql_variant) AS MARKS, 
           CAST(COMMENT AS sql_variant) AS COMMENT, 
           CAST(convert(varchar(10), TDATE ,120) AS sql_variant) AS TDATE,
           dense_rank() over(partition by STD order by MARKS) as rnk
    FROM Exam
) AS subquery
UNPIVOT
(
    VALUE
    FOR COLUMN_NAME IN (SUBJECT, MARKS, COMMENT, TDATE)
) AS unpvt


STD
COLUMN_NAME
VALUE


ST1
SUBJECT1
MATH

ST1
MARKS1
25%

ST1
COMMENT1
POOR

ST1
TDATE1
2021-02-01

ST1
SUBJECT2
SCIENCE

ST1
MARKS2
56%

ST1
COMMENT2
PASS

ST1
TDATE2
2021-02-04

ST1
SUBJECT3
ENGLISH

ST1
MARKS3
88%

ST1
COMMENT3
DIST

ST1
TDATE3
2021-02-02


DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + quotename(concat(c.column_name, cj.rn))
            from INFORMATION_SCHEMA.COLUMNS c
            cross join (
                select row_number() over(order by subject) rn
                from (select distinct subject from exam) e
                ) cj
            WHERE c.TABLE_NAME = 'Exam'
            AND c.column_name <> 'STD'
            order by cj.rn, c.ORDINAL_POSITION
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'SELECT STD, ' + @cols + ' 
            from 
            (
                SELECT STD, 
                       concat(COLUMN_NAME,rnk) as COLUMN_NAME, 
                       VALUE
                FROM
                (
                    SELECT STD, 
                           CAST(SUBJECT AS sql_variant) AS SUBJECT, 
                           CAST(MARKS AS sql_variant) AS MARKS, 
                           CAST(COMMENT AS sql_variant) AS COMMENT, 
                           CAST(convert(varchar(10), TDATE ,120) AS sql_variant) AS TDATE,
                           dense_rank() over(partition by STD order by MARKS) as rnk
                    FROM Exam
                ) AS subquery
                UNPIVOT
                (
                    VALUE
                    FOR COLUMN_NAME IN (SUBJECT, MARKS, COMMENT, TDATE)
                ) AS unpvt
            ) x
            pivot 
            (
                max(VALUE)
                for COLUMN_NAME in (' + @cols + ')
            ) p ';

EXECUTE(@query);


STD
SUBJECT1
MARKS1
COMMENT1
TDATE1
SUBJECT2
MARKS2
COMMENT2
TDATE2
SUBJECT3
MARKS3
COMMENT3
TDATE3


ST1
MATH
25%
POOR
2021-02-01
SCIENCE
56%
PASS
2021-02-04
ENGLISH
88%
DIST
2021-02-02


DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + quotename(concat(c.column_name, cj.rn))
from INFORMATION_SCHEMA.COLUMNS c
cross join (
select row_number() over(order by subject) rn
from (select distinct subject from exam) e
  ) cj
WHERE c.TABLE_NAME = 'Exam'
AND c.column_name <> 'STD'
order by cj.rn, c.ORDINAL_POSITION
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'SELECT ' + @cols + ' FROM Exam';

select @cols;


(No column name)


[SUBJECT1],[MARKS1],[COMMENT1],[TDATE1],[SUBJECT2],[MARKS2],[COMMENT2],[TDATE2],[SUBJECT3],[MARKS3],[COMMENT3],[TDATE3]
STD	COLUMN_NAME	VALUE
ST1	SUBJECT1	MATH
ST1	MARKS1	25%
ST1	COMMENT1	POOR
ST1	TDATE1	2021-02-01
ST1	SUBJECT2	SCIENCE
ST1	MARKS2	56%
ST1	COMMENT2	PASS
ST1	TDATE2	2021-02-04
ST1	SUBJECT3	ENGLISH
ST1	MARKS3	88%
ST1	COMMENT3	DIST
ST1	TDATE3	2021-02-02