如何把sqlserver中的列转换成行，行转换成列，显示。

        上次做活的时候碰到客户提的一个需求，就是要把数据库中得列转化为行来显示。呵呵，自己网上也找了一些解决方案，加上自己的一个解决方案，做一个总结，以免下次再碰到这种情况，也算是一种经验的积累把。

      

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