asked on

Display Data like Pivot Table with a SQL scrip

I have been using query analyser to extract raw data from MS SQL database and export the data to Excel, and re-arrange the data into meaningful monthly statistic report using Excel Pivot table funtion. The objective is to obtain the following information for Management Information and decison making :
Monthly Enrolment Statustic
(Col 1) (Col 2)
Course A course B
(Row 1) Jan 40 45
(Row 2) Feb 35 47
(Row 3) Mar 50 21

Would someone pleae advise how to use sql scrip to obtain the statistic and display them as shown above?

dqmq

SELECT Period, SUM(PIVOT.A) [Course A], sum(PIVOT.B) [Course B]
FROM
(SELECT DATENAME(YourDate) Period, --pivot stats by date
CASE YourCourse WHEN 'A' THEN YourCount ELSE 0 END A
CASE YourCourse WHEN 'B' THEN YourCount ELSE 0 END B
FROM YourTable ) PIVOT

dqmq

Oops, found typo. Try:

SELECT Period, SUM(PIVOT.A) [Course A], sum(PIVOT.B) [Course B]
FROM
(SELECT DATENAME(m,YourDate) Period, --pivot stats by date
CASE YourCourse WHEN 'A' THEN YourCount ELSE 0 END A
CASE YourCourse WHEN 'B' THEN YourCount ELSE 0 END B
FROM YourTable ) PIVOT

BenjaminWong

ASKER

Thanks for the pointers, however, tried but it didi niot work. So, I suppose PIVOT function is only available in SQL 2003 and above. Am I right? I am using SQL 2000. It looks like I have to resort to temp table to build up the data according to the format which I want before I use select command to display them. Any pointers on that?

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial