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