Cross-tab query in SQL Server

I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.

How would I create a cross-tab query in SQL Server?

Table for input to cross-tab query: [Inspection Data]

SerialNumber ParamName Result Pass/Fail

001 Test1 3.43 Pass

001 Test2 3.27 Pass

001 Test3 2.97 Pass

002 Test1 2.88 Pass

002 Test2 3.01 Pass

002 Test3 4.22 Fail

003 Test1 3.11 Pass

003 Test2 2.91 Pass

003 Test3 3.28 Pass

Cross-tab Query Output:

Test1 Test2 Test3 Pass/Fail

001 3.43 3.27 2.97 Pass

002 2.88 3.01 4.22 Fail

003 3.11 2.91 3.28 Pass

Thanks,

Sam

SELECT b.sno, b.test1, b.test2, b.test3, c.passfail FROM (select a.sNo
, min(case a.pName when 'test1' then result end) as [test1]
, min(case a.pName when 'test2' then result end) as [test2]
, min(case a.pName when 'test3' then result end) as [test3]
from dbo.pivotTable$ as a
group by a.sNo) b INNER JOIN (SELECT sNo, min(pOrf) AS PassFail
FROM pivotTable$ GROUP BY sNo) c ON c.sNo=b.sNo

For SQL Server 2005, you can:
SELECT p.sno, p.test1, p.test2, p.test3, b.PassFail
FROM (SELECT sno, pName, result
from pivotTable ) t
pivot (MAX(t.result) for t.pName in (test1,test2,test3)) AS p
INNER JOIN (SELECT sNo, min(pOrf) AS PassFail
FROM pivotTable$ GROUP BY sNo) b ON p.sNo=b.sNo|||

I am having trouble getting the syntax correct when I try to implement the real query. It tells me "Incorrect syntax near the keyword 'ON'

Do you have any idea what it might be?

select b.[Work Order No], b.[Serial No], b.[Apex], b.[Height]], b.[Radius], b.[Visual], b.[IL], b.[BR], c.[PF]

from ((select a.[Work Order No], a.[Serial No]

, min(case a.[Parameter Name] when 'Apex Offset' then [Value] end) as [Apex]

, min(case a.[Parameter Name] when 'Fiber Height ( Spherical Fit )' then [Value] end) as [Height]

, min(case a.[Parameter Name] when 'Radius of Curvature' then [Value] end) as [Radius]

, min(case a.[Parameter Name] when 'Visual Inspection' then [Value] end) as [Visual]

, min(case a.[Parameter Name] when 'Insertion Loss' then [Value] end) as [IL]

, min(case a.[Parameter Name] when 'Back Reflection' then [Value] end) as [BR]

from dbo.[Inspection Data] as a

group by a.[Work Order No],[Serial No] ) b inner join ((select [Work Order No], [Serial No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No],[Serial No] ) c on c.[Work Order No]=b.[Work Order No] and c.[Serial No] = b.[Serial No]

Thank you,

Sam

|||

"...

group by a.[Work Order No],[Serial No] ) b inner join ((select [Work Order No], [Serial No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No],[Serial No] ) c on c.[Work Order No]=b.[Work Order No] and c.[Serial No] = b.[Serial No]"

Is this an extra parathenses (in red)?