Question

sql server如何将列名旋转成值，并且原来的值也转换成列？

如 a b c d
1 2 3 4 转换成 列 列
a 1
b 2
c 3
d 4

Answer 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

Answer 2

你的例子太泛泛了，没有代表性，不明确，我给你一个有代表性的例子，你自己根据你的需要改造一下。
比如有一张表table
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,sum(case when subject = '语文' then grade else null end) 语文,
sum(case when subject = '数学' then grade else null end) 数学,
sum(case when subject = '英语' then grade else null end) 英语
from table
group by student

这是比较常见的行列转换，一般case when就能解决了

Answer 3

select abc,num from table unpivot(num for abc in (a,b,c,d))a