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

n3w2sql gravatar image

n3w2sql
850 14 22 33

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1834

asked: May 25, 2012 at 12:03 PM

Seen: 723 times

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