Transpose data in SQL with new column

Question

I have a data like:

id maths phy chem 
1   50   60   21
2   60   80   22
3   80   90   23

Now I want it to look like:

sub   1   2   3
math 50  60  80 
phy  60  80  90
chem 21  22  23

my code:

select *
  from mrks
    pivot
(
  max(maths) 
  for id in ([1], [2], [3])
) piv

however I'm facing 2 issues

1. I am not able to transpose chem and phy values

2. Not able to group maths, phy and chem under subj

Answer 1

In SQL Server, you can unpivot with cross apply, then pivot using conditional aggregation:

select 
    col,
    max(case when id = 1 then val end) col1,
    max(case when id = 2 then val end) col2,
    max(case when id = 3 then val end) col3
from mytable t
cross apply (values ('maths', maths), ('phy', phy), ('chem', chem)) p(col, val)
group by col

Demo on DB Fiddle:

col   | col1 | col2 | col3
:---- | ---: | ---: | ---:
chem  |   21 |   22 |   23
maths |   50 |   60 |   80
phy   |   60 |   80 |   90