PERSON table:
PID     LN         FN
1       BANNER     BRUCE
2       BLAZE      JOHNNY
3       SUMMER     SCOTT

STUDENT table:
PID     STUDNUM
1       S000001
3       S000045      

EXAM_RESULTS table:
PERSON_ID    EXAM_DATE      SUBJECT_ID    SCORE     GRADE
1            2012-08-08         1          98        A+
1            2012-08-08         2          89        B+
2            2011-11-11         1          95        A 
2            2011-11-11         2          85        B-

SUBJECTS table:
SUBJECT_ID DESC
1          ALGEBRA
2          PHYSICS

STUDNUM  LN     FN    SUBJ    SCORE   GRADE    SUBJ    SCORE   GRADE
S000001  BANNER BRUCE ALGEBRA    98    A+   PHYSICS     89     B+

STUDNUM  LN     FN    SUBJ    SCORE   GRADE
S000001  BANNER BRUCE ALGEBRA    98    A+ 
S000001  BANNER BRUCE PHYSICS    89    B+

SELECT
	STUDNUM,
	LN,
	FN,
	MAX(CASE WHEN SUBJ ='ALGEBRA' THEN SUBJ END) AS SUBJ,
	MAX(CASE WHEN SUBJ ='ALGEBRA' THEN SCORE END) AS SCORE,
	MAX(CASE WHEN SUBJ ='ALGEBRA' THEN GRADE END) AS GRADE,
	MAX(CASE WHEN SUBJ ='PHYSICS' THEN SUBJ END) AS SUBJ,
	MAX(CASE WHEN SUBJ ='PHYSICS' THEN SCORE END) AS SCORE,
	MAX(CASE WHEN SUBJ ='PHYSICS' THEN GRADE END) AS GRADE
FROM
	YourTableOrQuery
GROUP BY
	STUDNUM,
	LN,
	FN;

quote:

---

Originally posted by marksquall

Dear visakh16 and sunitabeck,

Thank you, thank you, thank you!

I tried to attempt to use multiple WITH blocks just to "group" results in Physics and Algebra...but little success.
Again, thank you so much to the both of you.

Another question if you all don't mind? How to proper construct the WHERE clause? I want something like look for student who have taken the exams from 1/1/2010 to 1/1/2012 something like that.

Warm regards,

Mark Squall

"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

---

...
WHERE datefield >= '20100101'
AND datefield < '20120101'

PERSON table:
PID     LN         FN
1       BANNER     BRUCE
2       BLAZE      JOHNNY
3       SUMMER     SCOTT
4       PARKER     PETER
5       STORM      SUSAN

APPLICANT Table:
PID   APPLICANT_NUMBER
1       2012-01
2       2012-02
3       2012-03
4       2012-04
5       2012-05

quote:

---

In this example, record ID 2,4,5 have a NULL entry if I join them in student table,

---

CREATE TABLE APPLICANT
(
	PID                 INT,
	APPLICANT_NUMBER    VARCHAR(32),
	APPLICATION_STATUS  INT
);

SELECT
	p.PID,
	a.LN,
	p.FN
FROM
	PERSON p
	INNER JOIN APPLICANT a ON
		a.PID = a.PID
WHERE
	APPLICANT_STATUS = 1
ORDER BY
	LN,FN;

SELECT
	p.PID,
	a.LN,
	p.FN
FROM
	PERSON p
	INNER JOIN APPLICANT a ON
		a.PID = a.PID
WHERE
	APPLICANT_NUMBER IS NULL
ORDER BY
	LN,FN;