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
asked
May 25 '12 at 12:03 PM
in Default
n3w2sql
730
●
5
●
11
●
19