is case statement the only way

Hi

I need to generate a SQL report like below,its basically calculating the count of students

For

District Level

then for

Region Level

then for

Each School under a Region

Like the Display Below

District summary

Total

Male

Femal

Indian

White

Asian

--

--

--

Type1

22

33

22

11

11

11

23

11

13

Type2

2

…6

…7

;;;;

13

14

Region 1

Region 2...................Region 8

Each School

School1....School 15

Do I have to have

case for each Type for each Race

and then

for Each Levels

of

District

Region

And 500 Schools?

Please Help

Thanks

Could you post your DDL statements and some sample data? This will help us understand your problem and hopefully provide a solution....
|||

From what I understand you may be looking for the PIVOT operator which is available in SQL 2005.

http://technet.microsoft.com/en-us/library/ms177410.aspx

|||

SELECT distinct SCHOOL_REGION,SCHOOL_NUMBER,ETHNICITY,SCH.S_SCHL_NAME,

INTV.Intervention_ ID,

case

INTV.Intervention_ ID when '1' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

case

INTV.Intervention_ ID when '2' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

........

-

-

case

INTV.Intervention_ ID when....... '14' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

FROM STUDENT_DEMOGRAPHIC STDM

left join school_location SCH on STDM.school_number = SCH.S_SCHOOL_NUM

left join Meeting MTNG on STDM.student_id = MTNG.student_id

left join Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID

left join Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID

where INTV.Intervention_ID not in ('')

group by SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

ORDER BY SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

P.S ...here the inner case statement is for the horizontal column names in the report

and I need the totals for the Type 1 ..type2 ...type 14 which is my vertical column in my report ( the INTV.Intervention_ID is this field) which i use in outer case statement above