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