t-sql中pivot用法(行列转换)
来源:互联网 发布:淘宝如何免费上传视频 编辑:程序博客网 时间:2024/05/22 14:49
从另一张表找的3974行是张三,8319行是李四,3051行是王五;
从stu_score查id,stu_id两列,聚合求出 得出张三,李四,王五在stu_score中的记录条数
select [3974] as 张三, [8319] as 李四, [3051] as 王五
from
(select id,stu_id from stu_score) as s
pivot
(
count(id)
for stu_id in ([3974],[8319],[3051])
)as pvt
from
(select id,stu_id from stu_score) as s
pivot
(
count(id)
for stu_id in ([3974],[8319],[3051])
)as pvt
T-SQL Pivot Syntax
SELECT
[non-pivoted column], -- optional
[additional non-pivoted columns], -- optional
[first pivoted column],
[additional pivoted columns]
FROM (
SELECT query producing sql da
-- select pivot columns as dimensions and
-- value columns as measures from sql tables
) AS TableAlias
PIVOT
(
<aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc
FOR [<column name containing values for pivot table columns>]
IN (
[first pivoted column], ..., [last pivoted column]
)
) AS PivotTableAlias
select exam_name as 考试名称, [407] as 一班, [408] as 二班, [409] as 三班, [415] as 九班
from
(select dept_id, exam_name, [language]
from stu_score,stu_studentinfo
where stu_score.stu_id = stu_studentinfo.id) as t
pivot
(
avg([language])
for dept_id in ([407],[408],[409],[415])
)as pvt
from
(select dept_id, exam_name, [language]
from stu_score,stu_studentinfo
where stu_score.stu_id = stu_studentinfo.id) as t
pivot
(
avg([language])
for dept_id in ([407],[408],[409],[415])
)as pvt
结果如下:
考试名称一班二班三班九班考试一89.2688.3390.3685.25考试二82.2687.9880.3685.25期末81.2683.3380.3678.25 0 0
- t-sql中pivot用法(行列转换)
- SQL中PIVOT 行列转换
- SQL中PIVOT 行列转换
- SQL中PIVOT 行列转换
- SQL SERVER PIVOT 行列转换
- sql pivot 实现行列转换
- SQL行列转换PIVOT、UNPIVOT
- SQL中PIVOT 行列转换;UNPIVOT列转行
- T-SQL行列互转(PIVOT/UNPIVOT)
- Sql 行列转换 动态Sql(Pivot)
- PIVOT/UNPIVOT的用法(行列转换)
- SQL server2005中用pivot实现行列转换
- Sql server pivot应用 行列转换
- SQL Server 行列转换 Pivot UnPivot
- sql server 行列转换PIVOT 和 UNPIVOT
- SQL通过PIVOT/UNPIVOT实现行列转换
- sql server(PIVOT) 行列转换 .
- sql server(PIVOT) 行列转换
- GPRS模块SIM9001发送短信(发送长短信),包括PDU和text两种模式
- Qt 调用外部exe的方法
- 关于字符串转换日期的问题
- 当迷茫在大学泛滥成灾
- C++项目实战-小游戏-连连看(CUI)
- t-sql中pivot用法(行列转换)
- 《登岳阳楼》 杜甫
- 能很大程度减轻很多Web前端工程师的思想负担
- Javascript 闭包与作用域
- >>关于MVC与三层架构(转自ProLiuzt以方便以后查阅)
- Qt Meta-Object System
- sql with as 用法
- 20个神奇的jQuery插件和100个多优秀的jQuery资源
- 20140911