CODEWITHSUNDEEP
sql-server-2008pivot
user3090790
user3090790

Reputation: 836

Pivot result returning duplicate rows

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

Upvotes: 2

Views: 1423

Answers (1)

Taryn
Taryn

Reputation: 247650

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

Upvotes: 1

Related Questions