CrossTab - HELP - as I cannot use sum...........!

[scouser]

Hi there,

Can anyone help me? Yes, I know there are a lot of answers regarding 'crosstabs' for SQL but I cannot use these examples as I cannot simply sum the columns as I have strings as well as numbers in my data. Is there anyway I can get around this without using third party software?

I did manage to create a SQL statement that produced the desired result but I could not save it as the design preview did not support CASE...(!!).

Other attempts have created messages such as sub queries can only produce 1 result, but mine has many!

Any help would be appreciated

TIA,

Shirley

 

[tlbroadbent]

We need to know the structure of the table(s). Also, it would be helpful to see sample data and sample output as well as the SQL you have used successfully. What tool are you using to build your query? Terry L. Broadbent - DBA

http://tlbroadbent.home.attbi.com/index.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers."

 

[scouser]

Hi Terry

Thanks for your reply. I only have just started SQL (from an Access background). Any help would be appreciated. The data is like this (the subject is actually as a code number but will be assigned the actual subject name).

I think the matter is complicated by the fact that the results values are alphanumeric...


Name Results Subject

A Bloggs 5A English
A Bloggs 4 Maths
A Bloggs 5B Science
A Bloggs 5 Physics
A Bloggs E Biology
A Bloggs N Music
J Smith N English
J Smith 5B Maths
J Smith 5 Science
J Smith 5A Physics
J Smith 3 Biology
J Smith E Music

I would like the above data to look like this....

Name English Maths Science

A Bloggs 5A 4 5B
J Smith N 5B 5

TIA,

Shirley

 

[arrowhouse]

Do you have a table which links the subject code to the description?

Is it just English Maths and Science you are interested in or all subjects?

What would you expect to see for a subject that someone hasn't eg if you had A N Other with these results:

Name Results Subject

A N Other 5 English
A N Other 4 Maths

 

[scouser]

Hi

Thanks for your response. Yes, there is a table that matches subject code with the subject name e.g. 41 = reading, 42 = Comprehension. I was thinking of using an alias?

Yes, I would need to show all subjects (7), I was limited by space in the example

And if a pupil does not have a subject and result, a null will suffice.

TIA,

Shirley

 

[scouser]

hiya

I also forgot to show you the SQL I used, I need to save the view, but the query deigner does not support the "SQL CASE contruct", but it does run.....only two subjects being crossed here in the example.

SELECT [Pupil Name], MAX(CASE WHEN tblSubject = 41 THEN ks1_level ELSE NULL END) AS [Reading],
MAX(CASE WHEN tblSubject = 42 THEN ks1_level ELSE NULL END) AS [Comp],
FROM dbo.PupilsResults
GROUP BY [Pupil Name]

TIA

Shirley

 

[moonshadow]

Here's how I would do it:
Create View v_PupilResults as
select name,
case subjectcode when 41 then result end as ReadingResult,
case subjectcode when 42 then result end as ComprehensionResult,
etc. for each subjectcode
from tblResults

and the select to sum the results (can be put in a view):
Select name, max(ReadingResult) as reading,
max(ComprehensionResult) as comprehension,
etc.
from v_PupilResults group by name

 

[arrowhouse]

If you really can't use case, then one solution (as you already metion) would be to alias your subject table 7 times (ie once for each subject).
e.g.

select r.name, english.result, maths.result,...
FROM results r
LEFT OUTER JOIN
subject english
ON r.subject = english.subject and english.subject_code = <whatever your code for english is>
LEFT OUTER JOIN subject maths
ON r.subject = maths.subject and maths.subject_code = <whatever your code for maths is>
etc..


You have to be very careful with solutions like this - if you get more subjects etc you will need to extend the code to cope with them. Also if it is possible that a student may have more than one result for one particular subject you might not get the results you'd want - you may have to use aggregatation to get the best result or last result for the subject).

You can use the ISNULL(english.result,'NOT TAKEN') to translate the NULL values into something more meaningful.

 

[arrowhouse]

I should also have mentioned:

In MS SQL You should be able to use case in a view - but you do get the horrible query designer message when working through enterprise manager. You can run them in via the query analyser with the CREATE VIEW statement (you will need to drop the view first if you have already created it.

Using the case statement is definitely a prefered solution as it is so much neater & easier to maintain

 

[scouser]

Thanks for your help!

I will try using case in the query designer