Pivot/UnPivot 2 columns

Question

create table #t (marks int, studentname varchar (50), subject varchar (50))

insert into #t values (70, 'A1', 'React')

insert into #t values (75, 'A1', 'Angular')

insert into #t values (80, 'A1', 'Vue')

insert into #t values (60, 'A2', 'React')

insert into #t values (65, 'A2', 'Angular')

insert into #t values (68, 'A2', 'Vue')

insert into #t_values (77, 'A3', 'React')

insert into #t values (88, 'A3', 'Vue')

insert into #t values (82, 'A4', 'Angular')

insert into #t values (85, 'A4', 'Vue')

SELECT * from #t

I have the table with the above structure and as per image1. Can I pivot/unpivot last 2 columns like image2

image2.PNG

image1.PNG

Answer 1

Hi @Punnet Patel

You can try this.

;with CTE as(
  select * from #t as c
  pivot(max(marks) for subject in ([React],[Angular],[Vue])) as t)
select studentname as Class,React,Angular,Vue from CTE;

Output:

Best regards,

Percy Tang

Answer 2

SELECT studentname [class], MAX(Case when subject='React' then marks Else null end) [React]

, MAX(Case when subject='Angular' then marks Else null end) [Angular]

, MAX(Case when subject='Vue' then marks Else null end) [Vue]

from #t

group by studentname