1

I have a table with following data:

ID IDALUMNO ALUMNO IDTIPOEVALUACION TIPOEVALUACION NOTA 1 682 ALVITEZ MIRANDA, KAREN LIZET 1 PR1 13 2 682 ALVITEZ MIRANDA, KAREN LIZET 1 PR2 14 3 682 ALVITEZ MIRANDA, KAREN LIZET 1 PR3 NULL 4 682 ALVITEZ MIRANDA, KAREN LIZET 4 TR1 11 5 682 ALVITEZ MIRANDA, KAREN LIZET 6 PA1 14 6 682 ALVITEZ MIRANDA, KAREN LIZET 6 PA2 18 7 682 ALVITEZ MIRANDA, KAREN LIZET 6 PA3 17 8 682 ALVITEZ MIRANDA, KAREN LIZET 6 PA4 16

And using PIVOT

SELECT [ALUMNO], [PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4]
FROM
( SELECT * FROM ALUMNOSEVALUACIONES ) AS [SOURCE]
PIVOT
(
    AVG(NOTA)
    FOR [TIPOEVALUACION] IN ([PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4])
) AS PIVOTTABLE

I get the following result:

ALUMNO PR1 PR2 PR3 TR1 PA1 PA2 PA3 PA4 ALVITEZ MIRANDA, KAREN LIZET 13 NULL NULL NULL NULL NULL NULL NULL ALVITEZ MIRANDA, KAREN LIZET NULL 14 NULL NULL NULL NULL NULL NULL ALVITEZ MIRANDA, KAREN LIZET NULL NULL NULL NULL NULL NULL NULL NULL ALVITEZ MIRANDA, KAREN LIZET NULL NULL NULL 11 NULL NULL NULL NULL ALVITEZ MIRANDA, KAREN LIZET NULL NULL NULL NULL 14 NULL NULL NULL ALVITEZ MIRANDA, KAREN LIZET NULL NULL NULL NULL NULL 18 NULL NULL ALVITEZ MIRANDA, KAREN LIZET NULL NULL NULL NULL NULL NULL 17 NULL ALVITEZ MIRANDA, KAREN LIZET NULL NULL NULL NULL NULL NULL NULL 16

But I need this result:

ALUMNO PR1 PR2 PR3 TR1 PA1 PA2 PA3 PA4 ALVITEZ MIRANDA, KAREN LIZET 13 14 NULL 11 14 18 17 16

My question is whether you can get the results as they wish using PIVOT.

Please look at my demo

1

I haven't done the actual brainwork to articulate what the bug is, but I believe the following code may solve your problem.

with PvtData ( ALUMNO, TIPOEVALUACION, AvgNOTA ) as
    ( select 
        ALUMNO
        ,[TIPOEVALUACION]
        ,avg( nota ) as AvgNOTA
    from ALUMNOSEVALUACIONES
    group by ALUMNO
        ,[TIPOEVALUACION] )
select 
    * 
from PvtData
PIVOT
(
    sum(AvgNOTA)
    FOR [TIPOEVALUACION] IN ([PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4])
) AS PIVOTTABLE

Pre-aggregating the average appears to return the correct Data. I hope this helps you!

  • Great! It works perfect. Thank you very much! – jchero.sojo Jun 8 '15 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.