Cross Tab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cross Tab

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-20 : 15:13:33

Hi,
I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.

I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.

It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.

AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED

I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this

Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1

Can anyone help me with the the extra columns please.

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-20 : 15:14:45

Oops. Formatting went a bit wrong there.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-20 : 19:23:54

[code]
select AcademicYearId,
CourseCode,
Stidents = count(*),
Continuing = count(case when completionstatusid = 'CONTINUING' then 1 end),
Withdrawn = count(case when completionstatusid = 'WITHDRAWN' then 1 end),
Transferred= count(case when completionstatusid = 'TRANSFERRED' then 1 end),
Completed = count(case when completionstatusid = 'COMPLETED' then 1 end)
from table
group by AcademicYearId, CourseCode
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-21 : 00:31:39

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-21 : 05:35:03

quote:

Originally posted by khtan


select AcademicYearId,
       CourseCode,
       Stidents   = count(*),
       Continuing = count(case when completionstatusid = 'CONTINUING' then 1 end),
       Withdrawn  = count(case when completionstatusid = 'WITHDRAWN' then 1 end),
       Transferred= count(case when completionstatusid = 'TRANSFERRED' then 1 end),
       Completed  = count(case when completionstatusid = 'COMPLETED' then 1 end)
from   table
group by AcademicYearId, CourseCode

Thankyou. Will try this on Monday.
Dan

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-21 : 05:35:37

quote:
Originally posted by madhivanan

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Hi
I saw that after I'd posted it but being a newbie couldnt wokr it out.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-21 : 06:11:04

There you can find examples similar to Tan posted. Here is other method


select AcademicYearId,
       CourseCode,
       Stidents   = count(*),
       Continuing = SUM(case when completionstatusid = 'CONTINUING' then 1 else 0 end),
       Withdrawn  = SUM(case when completionstatusid = 'WITHDRAWN' then 1  else 0 end),
       Transferred= SUM(case when completionstatusid = 'TRANSFERRED' then 1  else 0 end),
       Completed  = SUM(case when completionstatusid = 'COMPLETED' then 1  else 0 end)
from   table
group by AcademicYearId, CourseCode

Madhivanan

Failing to plan is Planning to fail

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-21 : 11:07:04

quote:

Originally posted by madhivanan

There you can find examples similar to Tan posted. Here is other method


select AcademicYearId,
       CourseCode,
       Stidents   = count(*),
       Continuing = SUM(case when completionstatusid = 'CONTINUING' then 1 else 0 end),
       Withdrawn  = SUM(case when completionstatusid = 'WITHDRAWN' then 1  else 0 end),
       Transferred= SUM(case when completionstatusid = 'TRANSFERRED' then 1  else 0 end),
       Completed  = SUM(case when completionstatusid = 'COMPLETED' then 1  else 0 end)
from   table
group by AcademicYearId, CourseCode

Madhivanan

Failing to plan is Planning to fail

Thankyou. it may turn out to be more complex than this as I have other fields and joins to include but thabnks for your help so far.

BTW, I just realised you didnt mean to check the crosstab/pivot table article on here (which i noticed after i'd posted). I will have a look at the SQL server help file.

Daniel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-22 : 22:43:17

>>you didnt mean to check the crosstab/pivot table article on here

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-23 : 14:37:56

I managed to get my query working pretty well using the code you supplied. Thanks.

Adding the extra fields I mentioned created a bit of a problem as I havent used the SUM correctly on the extra fields but i'm going to have another look at it tomorrw.

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-24 : 04:24:06

This is what I have now (I need to change how the part in red views)

SELECT
TOP 100000 SD.AcademicYearID, O.Code, O.Name, O.TargetStudents, COUNT(SD.StudentDetailID) AS [Total Students],
Continuing = SUM(CASE WHEN E.CompletionstatusID = '1' THEN 1 ELSE 0 END),
Completed = SUM(CASE WHEN E.CompletionstatusID = '2' THEN 1 ELSE 0 END),
Withdrawn = SUM(CASE WHEN E.CompletionstatusID = '3' THEN 1 ELSE 0 END),
Transferred = SUM(CASE WHEN E.CompletionstatusID = '4' THEN 1 ELSE 0 END),
Cancelled = SUM(CASE WHEN E.CompletionstatusID = '7' THEN 1 ELSE 0 END),
[Exempt Under Law] = SUM(CASE WHEN E.FeeExemptionReasonID = '1' THEN 1 ELSE 0 END),
[Waived On Benefit] = SUM(CASE WHEN E.FeeExemptionReasonID = '4' THEN 1 ELSE 0 END),
[Paid In Full] = SUM(CASE WHEN E.FeeExemptionReasonID = '99' THEN 1 ELSE 0 END),
[Other Funding] = SUM(CASE WHEN E.FeeExemptionReasonID = '13' THEN 1 ELSE 0 END),
[Waived By College] = SUM(CASE WHEN E.FeeExemptionReasonID = '10' THEN 1 ELSE 0 END),
[Waived Level 2 Entitlement] = SUM(CASE WHEN E.FeeExemptionReasonID = '22' THEN 1 ELSE 0 END),
[Fee Is Zero] = SUM(CASE WHEN E.FeeExemptionReasonID = '19' THEN 1 ELSE 0 END)

FROM prosolution.dbo.Enrolment E INNER JOIN
prosolution.dbo.Offering O ON E.OfferingID = O.OfferingID INNER JOIN
prosolution.dbo.StudentDetail SD ON E.StudentDetailID = SD.StudentDetailID

GROUP BY O.Code, O.Name, O.TargetStudents, E.CompletionStatusID, E.FeeExemptionReasonID, SD.AcademicYearID

ORDER BY O.Code, SD.AcademicYearID

If I dont include the part in red italics It looks ok in that it has one line per year as follows (cut down for space)

Year Course Target TotalStudents Cont Comp Wdrawn
05/06 ATBWK1 15 15 8 6 1

Once I start including the red italics part I get extra lines like the following example

Year Course Target TotalStudents Cont Comp Wdrawn PIF Exempt
05/06 ATBWK1 15 7 0 7 0 7 0
05/06 ATBWK1 15 5 5 0 0 5 0
05/06 ATBWK1 15 3 0 0 3 0 3

What I'd like it to show is this

Year Course Target TotalStudents Cont Comp Wdrawn PIF Exempt
05/06 ATBWK1 15 15 5 7 3 12 3

Is this possible? I bet the formatting is out again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 04:35:47

Try

Select Year, Course, Sum(Target), sum(TotalStudents), sum(Cont), sum(Comp), sum(Wdrawn), sum(PIF), sum(Exempt)
from
(Your full query) T
Group by Year, Course

Madhivanan

Failing to plan is Planning to fail

chipembele
Posting Yak Master

106 Posts

Posted - 2006-10-24 : 08:08:16

Thankyou for your help Madhivanan. I managed to sort it. I had a rogue extra GROUP BY in there that was messing up my results.
Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources