make multirows into one row

[LMCRYER]

My view (below) currently provides all of the info I need, but on multi-rows. I need it all on one row.

SELECT
PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
(SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
decode (PLGR.PRD,
'10212' ,
'200203',
'10910',
'200901') AS MPM_PRD,
SUM_UNIT,
(nvl(PLGR.CUR_ACT ,
PLGR.PRD_ACT) ) AS AMT
FROM
CSIOWNER.PLGR_ELK PLGR,
CSIOWNER.PELM PELM
WHERE
PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
PLGR.ORG_ID = PELM.ORG_ID AND
PLGR.ORG_ID = '001' AND
PLGR.BID_CTGY <> '**' AND
PELM.COST_MODE = 'D'


CURRENT OUTPUT:

2984.11 1P N 2003-08 C 0.00
2984.11 1P N 2003-08 R 0.00
2984.11 2E N 2003-08 C 128.71
2984.11 2E N 2003-08 R 7.00
2984.11 2P N 2003-08 C 42.91
2984.11 2P N 2003-08 R 2.00

I need it to be:

2984.11 1P N 2003-08 0.00 0.00
2984.11 2E N 2003-08 128.71 7.00
2984.11 2P N 2003-08 42.91 2.00
.....

The criteria for the 1st column of numbers is SUM_UNIT = 'R'
and the second is SUM_UNIT = 'C'

How do I do this???

LMC



LMC

 

[cLFlaVA]

Try this?

SELECT 
  PLGR.PROJ_ELEM_ID,
  PLGR.BID_CTGY,
  (SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
  decode (PLGR.PRD,
  '10212' ,
  '200203',
   '10910',
  '200901') AS MPM_PRD,
  SUM_UNIT,
  SUM((nvl(PLGR.CUR_ACT ,
  PLGR.PRD_ACT) )) AS AMT
FROM 
  CSIOWNER.PLGR_ELK PLGR,
  CSIOWNER.PELM PELM
WHERE 
  PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
  PLGR.ORG_ID = PELM.ORG_ID AND
  PLGR.ORG_ID = '001' AND
  PLGR.BID_CTGY <> '**' AND
  PELM.COST_MODE = 'D' 
GROUP BY 
  PLGR.PROJ_ELEM_ID,
  PLGR.BID_CTGY,
  (SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
  decode (PLGR.PRD,
  '10212' ,
  '200203',
   '10910',
  '200901'),
  SUM_UNIT

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[LMCRYER]

Nope, that wont work - mostly because the columns need different criteria. I think I need a subquery but dont know how to do this?

The data for column 5 AND 6 comes from ONE db.field, and is split out by different codes in another field (so to speak).

Any other ideas?

LMC

 

[LMCRYER]

GOT IT!
That was really close, I just had to tweak my grouping a bit. Posted below is the final version of my view.
Thanks SO MUCH!!!

SELECT
PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
(SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
decode (PLGR.PRD,
'10212' ,
'200203',
'10301' ,
'200204',
'10302' ,
'200205',
'10303' ,
'200206',
'10304' ,
'200207',
'10305' ,
'200208',
'10306' ,
'200209',
'10307' ,
'200210',
'10308' ,
'200211',
'10309' ,
'200212',
'10310' ,
'200301',
'10311' ,
'200302',
'10312' ,
'200303',
'10401' ,
'200304',
'10402' ,
'200305',
'10403' ,
'200306',
'10404' ,
'200307',
'10405' ,
'200308',
'10406' ,
'200309',
'10407' ,
'200310',
'10408' ,
'200311',
'10409' ,
'200312',
'10410' ,
'200401',
'10411' ,
'200402',
'10412' ,
'200403',
'10501' ,
'200404',
'10502' ,
'200405',
'10503' ,
'200406',
'10504' ,
'200407',
'10505' ,
'200408',
'10506' ,
'200409',
'10507' ,
'200410',
'10508' ,
'200411',
'10509' ,
'200412',
'10510' ,
'200501',
'10511' ,
'200502',
'10512' ,
'200503',
'10601' ,
'200504',
'10602' ,
'200505',
'10603' ,
'200506',
'10604' ,
'200507',
'10605' ,
'200508',
'10606',
'200509',
'10607',
'200510',
'10608',
'200511',
'10609',
'200512',
'10610',
'200601',
'10611',
'200602',
'10612',
'200603',
'10701',
'200604',
'10702',
'200605',
'10703',
'200606',
'10704',
'200607',
'10705',
'200608',
'10706',
'200609',
'10707',
'200610',
'10708',
'200611',
'10709',
'200612',
'10710',
'200701',
'10711',
'200702',
'10712',
'200703',
'10801',
'200704',
'10802',
'200705',
'10803',
'200706',
'10804',
'200707',
'10805',
'200708',
'10806',
'200709',
'10807',
'200710',
'10808',
'200711',
'10809',
'200712',
'10810',
'200801',
'10811',
'200802',
'10812',
'200803',
'10901',
'200804',
'10902',
'200805',
'10903',
'200806',
'10904',
'200807',
'10905',
'200808',
'10906',
'200809',
'10907',
'200810',
'10908',
'200811',
'10909',
'200812',
'10910',
'200901') AS MPM_PRD,
SUM(DECODE(SUM_UNIT,'R',nvl(PLGR.CUR_ACT ,PLGR.PRD_ACT),0)) AS R_AMT,
SUM(DECODE(SUM_UNIT,'C',nvl(PLGR.CUR_ACT ,PLGR.PRD_ACT),0)) AS C_AMT
FROM
CSIOWNER.PLGR_ELK PLGR,
CSIOWNER.PELM PELM
WHERE
PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
PLGR.ORG_ID = PELM.ORG_ID AND
PLGR.ORG_ID = '001' AND
PLGR.BID_CTGY <> '**' AND
PELM.COST_MODE = 'D'
group by PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
PLGR.PRD




LMC

 

[cLFlaVA]

It's not necessarily a good idea to hard-code all this data in there...

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[LMCRYER]

I know, but I need it controlled within Oracle, and not within the VB app that is being built. I would really prefer NOT to do it this way, but the higher powers that be wanted that.... =(

LMC