February 19, 2004 at 2:28 pm
Hello,
I have set up a Cross Tab Report as follows:
*******************************************
Select
TS.Permnum,
SD.LastName + ', ' + SD.FirstName as Student,
Sum(Case Cast(LTrim(replace(QNum,'Q',''))as smallint) When 1 Then Cast(Score as smallint) End) as Q1,
Sum(Case Cast(LTrim(replace(QNum,'Q',''))as smallint) When 2 Then Cast(Score as smallint) End) as Q2,
Sum(Case Cast(LTrim(replace(QNum,'Q',''))as smallint) When 3 Then Cast(Score as smallint) End) as Q3,
Sum(Case Cast(LTrim(replace(QNum,'Q',''))as smallint) When 4 Then Cast(Score as smallint) End) as Q4,
Sum(Case Cast(LTrim(replace(QNum,'Q',''))as smallint) When 78 Then Cast(Score as smallint)Else NULL End) as Q78
From
tblRC_StandardsTestScores03_04 TS
Inner Join Student_Data_Main SD On TS.Permnum = SD.Permnum
Where TS.Permnum = 2000025822
and TS.TestShortname = 'SFM2'
Group By TS.Permnum, SD.Lastname, SD.FirstName
*********************************************
In the code, I have set up the last CASE statement to evaluate 'QNum' 78, and if the value is NULL, then column 78 will express NULL for that record. In the case of the particular record that I am testing, QNum will be null because a QNum value of 78 does not exist.
The code works fine like it is now, but I need to modify the code so that if a 'QNum' is NULL, then the column should not appear in the report at all.
I might also be able to handle NULL columns in the client application, but it would be much better if I could handle that here.
Thanks for your help!
CSDunn
February 19, 2004 at 3:21 pm
That's a job for the FRONT END
but if you insist:
If Exists (select * from
tblRC_StandardsTestScores03_04 TS
Inner Join Student_Data_Main SD On TS.Permnum = SD.Permnum
Where TS.Permnum = 2000025822
and TS.TestShortname = 'SFM2'
and Qnum Like 'Q[78]%')
begin
--... Type your query as above
end
else
begin
--... Remove the unwanted column
end
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply