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

 

 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 data for pivot

  -- 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

结果如下:

考试名称	一班	二班	三班	九班
考试一	89.26	88.33	90.36	85.25
考试二	82.26	87.98	80.36	85.25
期末	81.26	83.33	80.36	78.25