Pivot result returning duplicate rows

Question

With this query, I am getting result with null values and duplicate Ids...

SELECT QuesId,QuesName,[Ketan Mevada],[Parvej],[Parvez Vahora]  
FROM (
    SELECT tbl_EvolutionAnswer.QuesId,tbl_QuestionMaster.Name as QuesName,
      dbo.Evaluation_Calculation_CourseWise(tbl_EvolutionAnswer.QuesId,34,'Course-Green Course-2045',1065, tbl_EvolutionAnswer.TrainerId ) as Average,
      tbl_EvolutionAnswer.TrainerId,
      tbl_TrainerMaster.Name as TrName  
   from tbl_EvolutionAnswer 
   inner join tbl_TrainerMaster 
      on tbl_EvolutionAnswer.TrainerId = tbl_TrainerMaster.Id 
   inner join tbl_QuestionMaster 
      on tbl_EvolutionAnswer.QuesId = tbl_QuestionMaster.QuestionId 
   where tbl_EvolutionAnswer.EvolId =34 
     and tbl_EvolutionAnswer.TrainerId <> 0 
     and tbl_EvolutionAnswer.CourseId = 'Course-Green Course-2045' 
     and tbl_EvolutionAnswer.SchID = 1065 
) as Books
PIVOT (
    MAX(Average) FOR TrName IN ([Ketan Mevada],[Parvej],[Parvez Vahora])
) as Result

Following images shows result of query..

I need Following Output

QuesId QuesName                   Ketan Mevada Parvej Parvez Vohra
122    Did your trainer answer... 2            3      2
123    was your trainer activ..   1            4      3

Inner Query Result

If you just select the data from your inner query, what is the result? It appears that you have some sort of unique value in each row that is impacting the grouping of the PIVOT. But without seeing the data, I'd be guessing which column. From the looks of it maybe tbl_EvolutionAnswer.TrainerId is the column you need to remove. You should only include the columns needed for the final result in your pivot. — Taryn
– Taryn, Commented Feb 12, 2015 at 11:19

Taryn · Accepted Answer · 2015-02-12 11:46:05Z

1

It appears that you have a column inside your subquery that is unique and it is causing the grouping of the aggregate function to be skewed. When you are using the PIVOT function, you should only include the columns needed for the PIVOT and the final select list, otherwise you run the risk of the end result being spilt over multiple rows.

It looks like the column you need to remove is tbl_EvolutionAnswer.TrainerId. Making your actual query:

SELECT QuesId,QuesName,[Ketan Mevada],[Parvej],[Parvez Vahora]  
FROM 
(
    SELECT tbl_EvolutionAnswer.QuesId,
     tbl_QuestionMaster.Name as QuesName,
     dbo.Evaluation_Calculation_CourseWise(tbl_EvolutionAnswer.QuesId,34,'Course-Green Course-2045',1065, tbl_EvolutionAnswer.TrainerId ) as Average,
     tbl_TrainerMaster.Name as TrName  
    from tbl_EvolutionAnswer 
    inner join tbl_TrainerMaster 
      on tbl_EvolutionAnswer.TrainerId = tbl_TrainerMaster.Id 
    inner join tbl_QuestionMaster 
      on tbl_EvolutionAnswer.QuesId = tbl_QuestionMaster.QuestionId  
    where tbl_EvolutionAnswer.EvolId =34 
      and tbl_EvolutionAnswer.TrainerId <> 0 
      and tbl_EvolutionAnswer.CourseId = 'Course-Green Course-2045' 
      and tbl_EvolutionAnswer.SchID = 1065 
) as Books
PIVOT (
    MAX(Average) FOR TrName IN ([Ketan Mevada],[Parvej],[Parvez Vahora])
) as Result

answered Feb 12, 2015 at 11:46

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shad Over a year ago

Won't TrainerId will be left out in the final result set? What if we need it.

Collectives™ on Stack Overflow

Pivot result returning duplicate rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related