November 3, 2009 at 5:01 pm
HI,
I have created my first pivot report! Yay!
However, I'm getting this:
bnoun, upp, side1, side2
1550B-4001, 2, NULL, AOI-55
1550B-4001, 2, IP-55, NULL
1577-4001, 3, NULL, CP-42
1577-4001, 3, IP-12, NULL
When I want this:
1550B-4001, 2, IP-55, AOI-55
1577-4001, 3, IP-12, CP-42
Here's my code.
SELECT TOP 100 PERCENT bnoun, upp, [1] AS side1, [2] AS side2
FROM (SELECT BottleneckTime_2.noun as bnoun,
upp, side,bottleneckmachine,maxofpanelsecs,
bottleneckmachine + '-' + convert(nvarchar(10),convert(int,maxofpanelsecs/upp)) as x
FROM
bottlenecktime_2 inner join latestrevmaster
on bottlenecktime_2.noun=latestrevmaster.noun
)as p
PIVOT (max(x) FOR side IN ([1], [2])) AS pvt
Please help me fix it!
Thanks
PS: Also, I apoligize for the comma-delimiting in the result set. If you can show me how to more cleanly post the result set, I'd appreciate it.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
November 4, 2009 at 12:42 am
Hi,
Try taking out the 'bottleneckmachine' and 'maxofpanelsecs' fields from your subselect.
SELECT TOP 100 PERCENT bnoun, upp, [1] AS side1, [2] AS side2
FROM (SELECT BottleneckTime_2.noun as bnoun,
upp, side,
--bottleneckmachine,maxofpanelsecs,
bottleneckmachine + '-' + convert(nvarchar(10),convert(int,maxofpanelsecs/upp)) as x
FROM
bottlenecktime_2 inner join latestrevmaster
on bottlenecktime_2.noun=latestrevmaster.noun
)as p
PIVOT (max(x) FOR side IN ([1], [2])) AS pvt
November 4, 2009 at 1:41 am
you could do a group by on
bnoun, upp,
And
Min( [1] ) AS side1,
Min( [2] ) AS side2
November 11, 2009 at 6:45 pm
Thanks Grasshopper, removing from the subselect did the trick. Although I'm going to have to stare at it for awhile to understand why.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply