Hi to all,
I have the following structure of table A
SEM_KEY COURSE_CODE GENDER STUD_YEAR
======= =========== ====== =========
1 BSCS 1 1
1 BSCS 1 2
1 BSIT 2 1
1 BSIT 2 2
1 BSIT 1 3
where gender 1=Male; 2=Female
I want to obtain the following output
COURSE 1M 1F 2M 2F 3M 3F
====== == == == == == ==
BSCS 1 0 1 0 0 0
BSIT 1 0 0 1 1 0
where 1M = total for male 1st year.
1F = total for female 1st year
the output serves as a summary to view the total # of records
in each course, gender, and year.
SELE COURSE_CODE, [1M]
FROM
(SELECT COURSE_CODE, COUNT(*) AS [1M]
FROM dbo.VW_SUMMARY
WHERE (COURSE_CODE = 'BSCS') AND (STUD_YEAR = 1) AND (GENDER = 1) AND (SEM_KEY = 1)
GROUP BY COURSE_CODE, STUD_YEAR, GENDER) A
Any help will be appreciated.
SELECT COURSE_CODE
,SUM(CASE WHEN GENDER=2 AND STUD_YEAR=1 THEN 1 END) AS 1F
,SUM(CASE WHEN GENDER=1 AND STUD_YEAR=1 THEN 1 END) AS 1M
,SUM(CASE WHEN GENDER=2 AND STUD_YEAR=2 THEN 1 END) AS 2F
,SUM(CASE WHEN GENDER=1 AND STUD_YEAR=2 THEN 1 END) AS 2M
,SUM(CASE WHEN GENDER=2 AND STUD_YEAR=3 THEN 1 END) AS 3F
,SUM(CASE WHEN GENDER=1 AND STUD_YEAR=3 THEN 1 END) AS 3M
FROM dbo.VW_SUMMARY
GROUP BY COURSE_CODE
