Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

Correct output with where condition inside VIEW


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([1],'') as Oper1,
    isnull([2],'') as Oper2,
    isnull([3],'') as Oper3,
    isnull([4],'') as Oper4,
    isnull([5],'') 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 ([1],[2],[3],[4],[5])
) 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

enter image description here

share

migration rejected from stackoverflow.com Sep 29 '13 at 12:56

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Max Vernon, Paul White, Mat, Mark Storey-Smith, Mikael Eriksson Sep 29 '13 at 12:56

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

comments disabled on deleted / locked posts