Self Join Query Help - complex 1

[hblabonte]

Hopefully someone out there can point me in the right direction!

I've got a table of test scores containing the following data with up to five different test types:

ID, Test_type, score, test_date, grade
000008710 CR 50 10/2004 11
000008710 MATH 62 10/2004 11
000008710 SI 17 10/2004 11
000008710 WS 63 10/2004 11

Notice this person does not have a score for the VBRL test, which is fine.

I need to produce an output file such as:
ID, CR_score, Math_score, SI_Score, VBRL_score, WS_score, test_date, grade (all on one row)

000008710, 50, 62, 17, , 63, 10/2004, 11

I can query the table, and self join for the particular test scores. The problem is as soon as I specify for the VBRL test, my whole query returns no rows, because this person has no test for that type. Even with an outer join, it's not working. Thoughts, help?!

My simplified example:

select
people_id, test_type, raw_score as 'Any_score', CRH_TESTSCORES.test_date, CRH_TESTSCORES.class_level,
-- SI.raw_score as 'SI_score',
-- VBRL.raw_score as 'VBRL_score'


from CRH_TESTSCORES

LEFT outer join dbo.CRH_TESTSCORES VBRL on
CRH_TESTSCORES.people_id = VBRL.people_id AND
CRH_TESTSCORES.test_date = VBRL.test_date

--left outer join dbo.CRH_TESTSCORES SI on
-- CRH_TESTSCORES.people_id = SI.people_id AND
-- CRH_TESTSCORES.test_date = SI.test_date


where CRH_TESTSCORES.people_id = '000008710' and
(CRH_TESTSCORES.test_id = 'psat') --get all test scores
and (VBRL.test_id = 'psat'and VBRL.test_type = 'VBRL') --pull only VBRL scores

 

[vongrunt]

Try this:

select ID,
	max(case when test_type='CR' then score end) as CR_score,
	max(case when test_type='MATH' then score end) as MATH_score,
	max(case when test_type='SI' then score end) as SI_score,
	max(case when test_type='WS' then score end) as WS_score,
	test_date, grade
from myTable
group by ID, test_date, grade

One thing to consider here are 'missing' values. If one ID has no any of four expected test types, MAX() will ignore NULL value and raise ANSI warning. You can eliminate that by using ELSE <someblankvalue> within CASE statement, with optional NULLIF() if NULLs are necessary in final result.

Another thing is database design itself. Test_date and grade values must be identical for all rows with same ID, otherwise things will fall apart.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hblabonte]

Thank you, thank you, thank you!!!!! That's exactly what I needed!