July 2, 2012 at 11:56 am
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
July 2, 2012 at 12:48 pm
...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
July 2, 2012 at 11:39 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply