如何在Pivot中动态列创建的Select语句中写入where条件

坦尼盖

在这里如何在这里写下我想在哪里条件仅选择特定考试ID的位置，在下面提到

DECLARE @cols NVARCHAR(2000);
    SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                    '],[' + t2.PartID
                            FROM    StudentAnswer AS t2
                            ORDER BY '],[' + t2.PartID
                            FOR XML PATH('')
                          ), 1, 2, '') + ']'

    DECLARE @Query NVARCHAR(4000);
    SET @Query= N' SELECT ExamID,StudentID,'+@cols+'
    FROM 
    (SELECT   StudentID,PartID,ExamID
                      , CASE WHEN Ans=CorrectAns THEN 1 WHEN Ans!=CorrectAns THEN 0 END as Result
              FROM StudentAnswer  **//Where Condtion**
            ) p 
             PIVOT ( SUM(Result)
                        FOR PartID 
                          IN ('+@cols+')
                      )  AS pvt;'
    EXECUTE(@Query);

坦尼盖

我用这个得到了答案。

DECLARE @cols NVARCHAR(2000);
    SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                    '],[' + t2.PartID
                            FROM    StudentAnswer AS t2
                            ORDER BY '],[' + t2.PartID
                            FOR XML PATH('')
                          ), 1, 2, '') + ']'

    DECLARE @Query NVARCHAR(4000);
    SET @Query= N' SELECT ExamID,StudentID,'+@cols+'
    FROM 
    (SELECT   StudentID,PartID,ExamID
                      , CASE WHEN Ans=CorrectAns THEN 1 WHEN Ans!=CorrectAns THEN 0 END as Result
              FROM StudentAnswer WHERE ExamID=''EX201411E1''**// Here Use the tow single Qutation**``
            ) p 
             PIVOT ( SUM(Result)
                        FOR PartID 
                          IN ('+@cols+')
                      )AS pvt;'                    
    EXECUTE(@Query);