-
OK, this should be an easy one, I don't know why I can't figure it out though.
I have:
SELECT
PERFMR.PROJ_ID
, DECODE(PERFMR.ROLE_TYPE_CD, 'TYPE_DESK', PERSON.LAST_NM, NULL) TD
, DECODE(PERFMR.ROLE_TYPE_CD, 'SPECIFICATION_WRITER', PERSON.LAST_NM, NULL) SW
, DECODE(PERFMR.ROLE_TYPE_CD, 'CONTRACTS_ANALYST', PERSON.LAST_NM, NULL) CA
FROM
PERFMR
, PERSON
WHERE
PERFMR.PERSON_ID = PERSON.PERSON_ID
and PERFMR.END_DT Is Null
AND PERFMR.ROLE_TYPE_CD IN ('TYPE_DESK','SPECIFICATION_WRITER','CONTRACTS_ANALYST')
group BY PERFMR.PROJ_ID
, DECODE(PERFMR.ROLE_TYPE_CD, 'TYPE_DESK', PERSON.LAST_NM, NULL)
, DECODE(PERFMR.ROLE_TYPE_CD, 'SPECIFICATION_WRITER', PERSON.LAST_NM, NULL) SW
, DECODE(PERFMR.ROLE_TYPE_CD, 'CONTRACTS_ANALYST', PERSON.LAST_NM, NULL) CA
I want the results to look like:
1 a b c
2 a b c
3 a b c
etc...
instead, I get:
1 a - -
1 - b -
1 - - c
2 a - -
2 - b -
2 - - c
3 a - -
3 - b -
3 - - c
(no, I don't get dashes, it's for the purpose of spacing).
Anyway, I know this can be done, if anyone has any insights, it would be certainly welcomed.
-
If I'm not missing your point, you can try it like this:
SELECT
PERFMR.PROJ_ID
, MAX(DECODE(PERFMR.ROLE_TYPE_CD, 'TYPE_DESK', PERSON.LAST_NM, NULL)) TD
, MAX(DECODE(PERFMR.ROLE_TYPE_CD, 'SPECIFICATION_WRITER', PERSON.LAST_NM, NULL)) SW
, MAX(DECODE(PERFMR.ROLE_TYPE_CD, 'CONTRACTS_ANALYST', PERSON.LAST_NM, NULL)) CA
FROM
PERFMR
, PERSON
WHERE
PERFMR.PERSON_ID = PERSON.PERSON_ID
and PERFMR.END_DT Is Null
AND PERFMR.ROLE_TYPE_CD IN ('TYPE_DESK','SPECIFICATION_WRITER','CONTRACTS_ANALYST')
group BY PERFMR.PROJ_ID;
HTH,
-
