Pivot Query Problem

• BlackKnight

  SSC Journeyman

  Points: 79

  More actions

  May 12, 2008 at 10:23 am

  #187660

  Hi,

  I am recording match results between various teams in various tournaments.

  My columns for the score table are HomeTeam, AwayTeam, Tournament, HomePoints, AwayPoints.

  e.g.

  HomeTeam|AwayTeam|Tournament|PointsHome|PointsAway

  ----------------------------------------------------------

  A B T1 1 2

  A B T2 3 4

  I want to pivot this to produce a table like:

  HomeTeam|AwayTeam|T1_HomePoints|T1_AwayPoints|T2_HomePoints|T2_AwayPoints

  ------------------------------------------------------------------------------------

  A B 1 2 3 4

  Can I do this in SQL Server 2005?

  Thanks

• GSquared

  SSC Guru

  Points: 260824

  More actions

  May 12, 2008 at 1:35 pm

  #814049

  Yes, it can be done. Take a look at the Pivot command in Books Online. That might have what you want.

  If not, you can do something like this:

  select t1.HomeTeam, t1.AwayTeam, t1.HomePoints, t1.AwayPoints, t2.HomePoints, t2.AwayPoints

  from dbo.Table t1

  inner join dbo.Table t2

  on t1.HomeTeam = t2.HomeTeam

  and t1.AwayTeam = t2.AwayTeam

  and t1.Tournament < t2.Tournament

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Alasdair Thomson

  Hall of Fame

  Points: 3630

  More actions

  May 12, 2008 at 1:35 pm

  #814050

  Yes in 2005 you can, although I had real issues working it out. But only used once... Here is the code I used...

  -- Fixed Column Crosstab with Category Subtotal - PIVOT Method

  SELECT Category, South, NorthEast, MidWest, West,

  IsNull(South,0) + IsNull(NorthEast,0) + IsNull(MidWest,0) + IsNull(West,0) as Total

  FROM (Select Category, Region, Amount from RawData) sq

  PIVOT

  (Sum (Amount)

  FOR Region IN (South, NorthEast, MidWest, West)

  ) AS pt

  It uses a sub query too but should give you an idea of what you need.

• Saravanakumar.Rathinam

  Ten Centuries

  Points: 1365

  More actions

  May 13, 2008 at 6:19 am

  #814384

  I have a problem to make the below data in below format using pivot

  Type Week Mon Tue wed thu Fri Sat Sun

  AA1933504183810223932022NULL

  BB19 470 418 11 139 36 NULL NULL

  AA2021872557NULLNULLNULLNULLNULL

  Reqd report type

  Previous Week Current Week

  TYPE Mon Tue Wed Thu Fri Sat SunMon Tue Wed Thu Fri Sat Sun

  is it possible to create a tsql?

  thanks & regards

  Saravanakumar.R