Combining detail rows in result set

From: <vick9935_at_my-dejanews.com>
Date: Mon, 13 Jul 1998 22:54:26 GMT
Message-ID: <6oe372$7dr$1@nnrp1.dejanews.com>

The SQL statement at the end of this posting provides the following type of result set...

Activity JanFcst FebFcst MarFcst

030       SR1
030                SR2
030       SR3
030                          SR4
030                SR5

I would really like to return a modified result set (using stored procedure manipulations). The modified result set would be the following...

Activity JanFcst FebFcst MarFcst

030       SR1      SR2       SR4
030       SR3      SR5

Here is the SQL (if it helps):

SELECT "CP_PROJECT"."SEARCH_RING",
"CP_PROJ_SCHED_ACTIVITY"."SCHED_ACTIVITY_CODE",

DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'JAN


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "JANFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'FEB


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "FEBFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'MAR


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "MARFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'APR


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "APRFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'MAY


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "MAYFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'JUN


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "JUNFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'JUL


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "JULFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'AUG


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "AUGFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'SEP


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "SEPFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'OCT


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "OCTFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'NOV


'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "NOVFCST",


DECODE(TO_CHAR("FORECAST_DATE",'MON YYYY'),'DEC

'||:AS_FISCALYR,"CP_PROJECT"."SEARCH_RING",'') "DECFCST"
FROM "CP_PROJECT",
        "CP_PROJ_SCHED_ACTIVITY"

WHERE ("CP_PROJECT"."PROJ_NO" = "CP_PROJ_SCHED_ACTIVITY"."PROJ_NO")
 	 AND (TO_CHAR("FORECAST_DATE",'YYYY') = :AS_FISCALYR)
	 AND ("CP_PROJ_SCHED_ACTIVITY"."SCHED_ACTIVITY_CODE" IN (:AS_ACTIVITY))

ORDER BY "CP_PROJ_SCHED_ACTIVITY"."SCHED_ACTIVITY_CODE", "CP_PROJ_SCHED_ACTIVITY"."FORECAST_DATE" Any help is greatly appreciated. I've already posted similar questions...I am just hoping that the right presentation of the question will prompt a reply.

Brian in Seattle

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 13 1998 - 17:54:26 CDT