如何把sqlserver中的列转换成行,行转换成列,显示
来源:互联网 发布:淘宝店托管 公司 编辑:程序博客网 时间:2024/05/28 19:24
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
转自:http://m.blog.csdn.net/article/details?id=9138053
借签:http://www.studyofnet.com/news/295.html
- 如何把sqlserver中的列转换成行,行转换成列,显示
- 如何把sqlserver中的列转换成行,行转换成列,显示。
- 如何把sqlserver中的列转换成行,行转换成列,显示
- 把列内容转换成行内容 oracle
- Oracle将列转换成行
- sql 把列变成行显示
- 把列排成行
- ms sql 将列转换成行
- oracle 11g 查询 列转换成行
- sql 把行转换成合并列
- SQLServer中行列转换SQL
- 把查询结果列转换为行
- 004_008 Python 行转化成列,列转化成行
- 把列变成行的sql语句
- sqlserver把行转成列
- jquery如何把参数列严格转换成数组
- SQL行转换成列
- 行转换列(mssql)
- 通过eclipse创建maven项目
- ACM编程比赛入门题目之蛇形矩阵 CodeVS/wikioi 1160
- CSS ToolTip 实现方法1
- 【结论】【位运算】求1~n异或的结果(NKOJ 2466)
- Invert Binary Tree 递归
- 如何把sqlserver中的列转换成行,行转换成列,显示
- JavaScript数组
- 从零开始学_JavaScript_系列(16)——js系列<5>(正则表达式)
- DBSCAN算法详解
- spark groupByKey操作
- c++作业5
- 【GIT】使用GIT GUI 连接到conding码库教程
- Android中的自定义View与Notification
- poj之旅——1862