Help with a Pivot Table

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  November 1, 2007 at 6:47 am

  #747403

  Opus,

  You'll have to modify this to fit your needs, but this should get you there.

  DECLARE @studInfo TABLE (student VARCHAR(10), period CHAR(2), absents INT)

  INSERT @studInfo

  SELECT 'Student1', '2A', 1 union

  select 'Student1', '3A', 2 union

  select 'Student1', '4A', 3 union

  select 'Student1', '8B', 1 union

  select 'Student1', '5B', 1 union

  select 'Student2', '7B', 1 union

  select 'Student3', '1A', 2 union

  select 'Student4', '1A', 2 union

  select 'Student4', '2A', 2 union

  select 'Student4', '5B', 4 union

  select 'Student4', '6B', 1

  SELECT

  student

  ,ISNULL([1A] , 0) AS [1A]

  ,ISNULL([2A] , 0) AS [2A]

  ,ISNULL([3A] , 0) AS [3A]

  ,ISNULL([4A] , 0) AS [4A]

  ,ISNULL([5B] , 0) AS [5B]

  ,ISNULL([6B] , 0) AS [6B]

  ,ISNULL([7B] , 0) AS [7B]

  ,ISNULL([8B] , 0) AS [8B]

  FROM

  (SELECT

  student

  ,period

  ,absents

  FROM

  @studInfo) AS dataToPivotTable

  PIVOT

  (SUM(absents) -- value to aggregate

  FOR period -- what are your horizontal columns (fields)

  IN ([1A]

  ,[2A]

  ,[3A]

  ,[4A]

  ,[5B]

  ,[6B]

  ,[7B]

  ,[8B])

  ) AS whatToPivotOnTable

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• Opus

  Default port

  Points: 1419

  More actions

  November 1, 2007 at 7:07 am

  #747409

  Thanks!