Crosstabing AVG and checking with CASE

  • This problem has turned me every way but loose 🙂 A few cte tables summarize my data. I then need to crosstab the results and a final select statement checks for NULLs in the result set. I looked at Jeff's excellent crosstab article, but I wasn't sure how to crosstab with averages. Maybe someone can help...

    I was tempted to not post the final cte query (cte_pivot) because it doesn't work. But I thought it might show what I'm attempting. I verified the example works down to cte_summary. What needs to happen next is a crosstab of the averages from cte_summary. Finally, a select statement checks for NULLs in the result set. If NULL is returned, 0 is put in its place (this is needed for SSRS).

    CREATE TABLE PVT_AVG

    (

    Stu_IDCHAR(10)NOT NULL,

    Adm_ExamCHAR(25)NOT NULL,

    Adm_Exam_DateDateNOT NULL,

    Adm_Exam_ScoreCHAR(6)NOT NULL

    )

    INSERT INTO PVT_AVG

    (

    Stu_ID,

    Adm_Exam,

    Adm_Exam_Date,

    Adm_Exam_Score

    )

    VALUES

    ('123456', 'Adm_MCT', '2009-01-01', '21'),

    ('123456', 'Adm_MCT', '2010-03-01', '23'),

    ('987654', 'Adm_GPA', '2008-04-15', '3.21'),

    ('123456', 'Adm_GPA', '2011-02-01', '3.11'),

    ('987654', 'Adm_MCT', '2008-10-01', '27'),

    ('987654', 'Adm_MCT', '2010-02-02', '23')

    /*Test the script*/

    SELECT * FROM PVT_AVG

    /*Partition Adm_Exam by Stu_ID in order to select most recent date*/

    WITH cte AS

    (

    SELECT Stu_ID,

    Adm_Exam,

    Adm_Exam_Date,

    Adm_Exam_Score,

    ROW_NUMBER() OVER(PARTITION BY Stu_ID, Adm_Exam ORDER BY Adm_Exam_Date desc) pos

    FROM PVT_AVG

    ),

    /*Select most recent date and average Adm_Exam_Score*/

    cte_summary AS

    (

    SELECT cte.Adm_Exam,

    AVG(CAST(cte.Adm_Exam_Score AS DECIMAL(8,2))) AS Avg_Score

    FROM cte

    WHERE cte.pos = 1

    GROUP BY cte.Adm_Exam

    ),

    /*Here is where the results need to be crosstabed/pivoted*/

    cte_pivot AS

    (

    SELECT

    [Adm_GPA] = SUM(CASE WHEN cte_summary.Adm_Exam='Adm_GPA' THEN cte_summary.Adm_Exam ELSE 0 END),

    [Adm_MCT] = SUM(CASE WHEN cte_summary.Adm_Exam='Adm_MCT' THEN cte_summary.Adm_Exam ELSE 0 END)

    FROM cte_summary

    )

    /*Case statements to check cte_pivot results for NULL and change to 0*/

    SELECT

    CASE WHEN cte_pivot.Adm_GPA IS NULL THEN 0 ELSE cte_pivot.Adm_GPA END AS [DO_GPA],

    CASE WHEN cte_pivot.Adm_MCT IS NULL THEN 0 ELSE cte_pivot.Adm_MCT END AS [DO-MCT]

    FROM cte_pivot

  • ...after rereading Jeff's article over lunch, I realized I can use MAX instead of SUM. So tweaking the THEN clauses, I came up with the below query that works! I apologize for taking up space - I actually worked on this all morning!

    /*Partition Adm_Exam by Stu_ID in order to select most recent date*/

    WITH cte AS

    (

    SELECT Stu_ID,

    Adm_Exam,

    Adm_Exam_Date,

    Adm_Exam_Score,

    ROW_NUMBER() OVER(PARTITION BY Stu_ID, Adm_Exam ORDER BY Adm_Exam_Date desc) pos

    FROM PVT_AVG

    ),

    /*Select most recent date and average Adm_Exam_Score*/

    cte_summary AS

    (

    SELECT cte.Adm_Exam,

    AVG(CAST(cte.Adm_Exam_Score AS DECIMAL(8,2))) AS Avg_Score

    FROM cte

    WHERE cte.pos = 1

    GROUP BY cte.Adm_Exam

    ),

    /*Here is where the results need to be crosstabed/pivoted*/

    cte_pivot AS

    (

    SELECT

    [Adm_GPA] = MAX(CASE WHEN cte_summary.Adm_Exam='Adm_GPA' THEN cte_summary.Avg_Score ELSE 0 END),

    [Adm_MCT] = MAX(CASE WHEN cte_summary.Adm_Exam='Adm_MCT' THEN cte_summary.Avg_Score ELSE 0 END)

    FROM cte_summary

    )

    /*Case statements to check cte_pivot results for NULL and change to 0*/

    SELECT

    CASE WHEN cte_pivot.Adm_GPA IS NULL THEN 0 ELSE cte_pivot.Adm_GPA END,

    CASE WHEN cte_pivot.Adm_MCT IS NULL THEN 0 ELSE cte_pivot.Adm_MCT END

    FROM cte_pivot

  • How about making it a little simple by avoiding all those CTEs like this:

    CREATE TABLE PVT_AVG

    (

    Stu_IDCHAR(10)NOT NULL,

    Adm_ExamCHAR(25)NOT NULL,

    Adm_Exam_DateDateNOT NULL,

    Adm_Exam_ScoreCHAR(6)NOT NULL

    )

    INSERT INTO PVT_AVG

    (

    Stu_ID,

    Adm_Exam,

    Adm_Exam_Date,

    Adm_Exam_Score

    )

    VALUES

    ('123456', 'Adm_MCT', '2009-01-01', '21'),

    ('123456', 'Adm_MCT', '2010-03-01', '23'),

    ('987654', 'Adm_GPA', '2008-04-15', '3.21'),

    ('123456', 'Adm_GPA', '2011-02-01', '3.11'),

    ('987654', 'Adm_MCT', '2008-10-01', '27'),

    ('987654', 'Adm_MCT', '2010-02-02', '23')

    /*Test the script*/

    Select

    Max(CASE WHEN Adm_Exam='Adm_GPA' THEN Adm_Exam ELSE '' END) As [DO_GPA],

    Max(CASE WHEN Adm_Exam='Adm_MCT' THEN Adm_Exam ELSE '' END) As [DO-MCT]

    FROM

    (Select Adm_Exam, AVG(CAST(Adm_Exam_Score AS DECIMAL(8,2))) AS Avg_Score From

    (SELECT Stu_ID, Adm_Exam, Adm_Exam_Date, Adm_Exam_Score, ROW_NUMBER() OVER(PARTITION BY Stu_ID, Adm_Exam ORDER BY Adm_Exam_Date desc) pos

    FROM PVT_AVG) As a

    Where a.pos = 1

    Group By Adm_Exam) As b

    This would help you in creating a Dynamic Pivot incase your Exams or the no. of Exams in your Table are prone to change.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply