matrix SQL

[latha9]

Hi ALL,
I have created a matrix sql using decode according to my requirement for reports.But for the same region has more than one need it is printing in 2 rows.but i want it in the same row.can any one please help me.i am sending my query and result.

SELECT umn.region, isp.isp_total,umn.unmet_total,
decode( umn.grid_name, '14e HOUSING RESOURCES', umn.unmet_total,0) housing_support_needs,
decode( umn.grid_name, '14a MENTAL HEALTH SERVICES', umn.unmet_total, 0) mental_health_services,
decode( umn.grid_name, '14j VOCATIONAL / EMPLOYMENT RESOURCES', umn.unmet_total, 0) employment_support,
decode( umn.grid_name, '14l TRANSPORTATION RESOURCES', umn.unmet_total, 0) transportation_needs,
decode( umn.grid_name, '14m PERSONAL GROWTH/COMMUNITY PARTICIPATION RESOURCES', umn.unmet_total, 0) social_needs

FROM

(SELECT substr(rgn.assoc_name,1,8) region, COUNT(DISTINCT da.peo_id)
isp_total

FROM DEPT_ASSESSMENT da, PEOPLE_X_ASSOCIATION pxa, ASSOCIATION rgn,

(SELECT peo_id, MAX(date_start) curr_assmt_date

FROM DEPT_ASSESSMENT

WHERE ins_id = 1989

AND date_end IS NULL

GROUP BY peo_id) ca

WHERE da.ins_id = 1989

AND da.date_end IS NULL

AND da.peo_id = ca.peo_id

--and da.peo_id in (select distinct PEO_ID from PEOPLE &wherecondition)

AND da.date_start = ca.curr_assmt_date

AND da.peo_id = pxa.peo_id

AND pxa.end_date IS NULL

AND pxa.assoc_type_id = 101

AND pxa.assoc_id = rgn.assoc_id

AND rgn.assoc_name NOT LIKE '%CHILDREN%'

GROUP BY substr(rgn.assoc_name,1,8)) isp,

(SELECT substr(assoc_name,1,8) region, grid_name, COUNT(DISTINCT um.peo_id) unmet_total

FROM eisdba.MH_ISP_UNMET_NEEDS_BY_DAYS um, PEOPLE_X_ASSOCIATION px,ASSOCIATION rg

WHERE unmet_yn = 'Y'

AND um.peo_id = px.peo_id

--and um.peo_id in (select distinct PEO_ID from PEOPLE &wherecondition)

AND px.end_date IS NULL

AND px.assoc_type_id = 101

AND px.assoc_id = rg.assoc_id

AND rg.assoc_name NOT LIKE '%CHILDREN%'

AND um.GRID_NAME in ('14a MENTAL HEALTH SERVICES','14e HOUSING RESOURCES','14j VOCATIONAL / EMPLOYMENT RESOURCES',

'14l TRANSPORTATION RESOURCES','14m PERSONAL GROWTH/COMMUNITY PARTICIPATION RESOURCES')

GROUP BY substr(assoc_name,1,8), grid_name) umn

where umn.region = isp.region

order by umn.region

result:

Region isp_total unmet_total housing_ mental employment transportation social
support_ health support needs needs
needs services

REGION 1 32 11 0 11 0 0 0
REGION 1 32 1 0 0 1 0 0
REGION 2 100 18 0 18 0 0 0
REGION 3 9 3 0 3 0 0 0
thanks,
latha