t-sql中pivot用法(行列转换)
从另一张表找的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.26 | 88.33 | 90.36 | 85.25 |
| 考试二 | 82.26 | 87.98 | 80.36 | 85.25 |
| 期末 | 81.26 | 83.33 | 80.36 | 78.25 |
