x
login about faq Site discussion (meta-askssc)

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 '12 at 12:03 PM in Default

n3w2sql gravatar image

n3w2sql
730 5 11 19

(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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1601

asked: May 25 '12 at 12:03 PM

Seen: 313 times

Last Updated: May 25 '12 at 12:09 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.