x

Pivot Table Joining

Hi I am trying to pivot join and cant work it out, I can get it to work with 2 but when I try adding the success into the query I cant get it to work, I want to show q1ontime, q1late, q1success but for 1,2,3 and 4 This is my code, any advise? many thanks Lee

 select success.Class,
 ontime.[1] as [Q1Ontime],
 late.[1]   as [Q1Late],
 Success.[1]as [Q1Success],
 ontime.[2] as [Q2Ontime],
 late.[2]   as [Q2Late],
 Success.[2]as [Q1Success],
 ontime.[3] as [Q3Ontime],
 late.[3]   as [Q3Late],
 Success.[3]as [Q1Success],
 ontime.[4] as [Q4Ontime],
 late.[4]   as [Q4Late]
 Success.[4]as [Q1Success],
 from
 (
 SELECT [Dept] as Class,
 [1],[2],[3],[4]
 FROM
 (
 SELECT [Dept]
       ,[Q]
       ,sum([Late]) Late
 FROM lm.dbo.[allb]
 WHERE  Inceptyear = 2012
   GROUP BY [Dept],[Q]
   ) a
   pivot
   (
   sum(late) 
   FOR Q IN ( [1],[2],[3],[4]) 
   ) AS PivotTable
   ) late
  inner join
   (
 SELECT [Dept] as Class,
 [1],[2],[3],[4]
 FROM
 (
 SELECT [Dept]
       ,[Q]
       ,sum([ontime]) ontime
 FROM lm.dbo.[allb]
 WHERE  Inceptyear = 2012
 GROUP BY [Dept],[Q]
   ) a
   pivot
   (
   sum(ontime) 
   for Q IN ( [1],[2],[3],[4]) 
   ) AS PivotTable
   ) ontime
   inner join
   (
 SELECT [Dept]
        ,[Q]
        ,sum([Success]) Success
 FROM lm.dbo.[allb]
 WHERE  Inceptyear = 2012
 GROUP BY [Dept],[Q]
   ) a
   pivot
   (
   sum(Success) 
   for Q IN ( [1],[2],[3],[4]) 
   ) AS PivotTable
   ) Success
   on ontime.class =success.class
more ▼

asked May 25, 2012 at 12:03 PM in Default

avatar image

n3w2sql
870 22 32 39

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091

asked: May 25, 2012 at 12:03 PM

Seen: 1287 times

Last Updated: May 25, 2012 at 12:09 PM

Copyright 2016 Redgate Software. Privacy Policy