I am facing a small issue while using PIVOT table. My problem is as following.
I have a single table with the procedures id and their steps and i want all the steps needs to be displayed in 5 column then next seq...
S18CW | 001.000 | **Laser Scribe | Mark Mask Layer (5500/100) |PTest w/rework, |dispo, & scrap WAIT state feature |Mark Photostrip |NBL Mask Layer (5500/100) |NBL Implant|**
Bold columns are steps for the lot 001.000 so I am using below query to get the above data
select flow, MainProcSeqNo, Oper, isnull(,'') as Oper1, isnull(,'') as Oper2, isnull(,'') as Oper3, isnull(,'') as Oper4, isnull(,'') as Oper5 from ( SELECT DISTINCT TOP (100) PERCENT fl.Flow, fl.CallProcTitle AS oper, fl.MainProcSeqNo, fl2.CallProcTitle, DENSE_RANK() OVER ( PARTITION BY fl.MainProcSeqNo order by CAST(fl2.MainProcSeqNo AS float) * 1000 - CAST(fl.MainProcSeqNo AS float) * 1000 ) AS nxt FROM dbo.PromisAllFlows AS fl LEFT OUTER JOIN dbo.PromisAllFlows AS fl2 ON fl2.Flow = fl.Flow where CAST(fl2.MainProcSeqNo AS float) * 1000 - CAST(fl.MainProcSeqNo AS float) * 1000 BETWEEN 1 AND 5000 **and fl.flow='S18CW'** ) as a pivot ( max(CallProcTitle) for nxt in (,,,,) ) as pvt order by MainProcSeqNo
My problem is when i use WHERE condition inside sub query it is working perfectly but if i use outside of view it gives me wrong output like all the blanks instead of data in pivot table