どのようにsqlserverの中の列を行に変換して、行を列に変換して、表示します.

前回仕事をしていたとき、データベースのカラムをローに変換して表示するというお客様のニーズに遭遇しました.ほほほ、自分のネット上でもいくつかの解決策を探して、自分の1つの解決策を加えて、1つの総括をして、次回このような状況に出会わないようにして、1種の経験の蓄積です.
      

create database arron
go
use arron
go

-- createTable init Data
create table students (
  name varchar(25),
  class varchar(25),
  grade int
)
insert into students values ('  ','  ',20)
insert into students values ('  ','  ',90)
insert into students values ('  ','  ',50)
insert into students values ('  ','  ',81)
insert into students values ('  ','  ',60)
insert into students values ('  ','  ',90)

-- solution1
select * from students 
pivot( 
max(grade) 
FOR [class] IN ([  ],[  ],[  ])
) AS pvt


-- solution2       

select A.Name,A.grade as   ,B.grade as   ,C.grade as   
from students A,students B,students C
where A.Name=B.Name and B.Name=C.Name
and A.class='  ' and B.class='  '
and C.class='  '

-- solution3 
select name,
max(case when s.class='  ' then s.grade end) as   ,
max(case when s.class='  ' then s.grade end) as   ,
max(case when s.class='  ' then s.grade end) as   
 from students s group by name


--  id     
create table students2 (
  id int primary key identity(1,1),
  name varchar(25),
  class varchar(25),
  grade int
)

insert into students2 values ('  ','  ',20)
insert into students2 values ('  ','  ',90)
insert into students2 values ('  ','  ',50)
insert into students2 values ('  ','  ',81)
insert into students2 values ('  ','  ',60)
insert into students2 values ('  ','  ',90)

--    solution1(   )
select * from students2 
pivot( 
max(grade) 
FOR [class] IN ([  ],[  ],[  ])
) AS pvt
--    solution2 (ok)
select A.Name,A.grade as   ,B.grade as   ,C.grade as   
from students A,students2 B,students2 C
where A.Name=B.Name and B.Name=C.Name
and A.class='  ' and B.class='  '
and C.class='  '
--    solution3 （ok）
select name,
max(case when s.class='  ' then s.grade end) as   ,
max(case when s.class='  ' then s.grade end) as   ,
max(case when s.class='  ' then s.grade end) as   
 from students s group by name


--unpivot     
create table test1(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test1 values(1,'a',1000,2000,4000,5000)
insert into test1 values(2,'b',3000,3500,4200,5500) 

--   sql
select * from test1

select id ,[name],[jidu],[xiaoshou] from test1
unpivot
(
 xiaoshou for jidu in
 ([q1],[q2],[q3],[q4])
)
as f

---    sql        sql
select id,[name],
jidu='Q1',
xiaoshou=(select Q1 from test1 where id=a.id)
from test1 as a
union
select id,[name],
jidu='Q2',
xiaoshou=(select Q2 from test1 where id=a.id)
from test1 as a
union
select id,[name],
jidu='Q3',
xiaoshou=(select Q3 from test1 where id=a.id)
from test1 as a
union
select id,[name],
jidu='Q4',
xiaoshou=(select Q4 from test1 where id=a.id)
from test1 as a