Sinan -- Thanks for the question regarding "Query", version 8.1.7.4.1

You Asked

Hi Tom,

I definetly remember seeing something similar to this on this site, but could not find. If I am right, could you direct me to the link please?

Thank you,
Sinan


here is my question.

-- Create table
create table ASKTOM_TASKS
(
  TSK_CODE     VARCHAR2(20),
  TSK_CLASS    VARCHAR2(5)
  TSK_CAT_CODE VARCHAR2(3)
)
/

insert into ASKTOM_TASKS VALUES ('CRU-SA-001',     'PM', 'CRU');
insert into ASKTOM_TASKS VALUES ('CRU-ML-003',     'PM', 'CRU');
insert into ASKTOM_TASKS VALUES ('PRI-CRU-EP-001', 'EP', 'CRU');
insert into ASKTOM_TASKS VALUES ('PRI-UPM-OP-001', 'OP', 'UPM');
insert into ASKTOM_TASKS VALUES ('UPM-AL-001',     'PT', 'UPM');
insert into ASKTOM_TASKS VALUES ('UPM-AL-002',     'PT', 'UPM');
insert into ASKTOM_TASKS VALUES ('UPM-WL-001',     'IN', 'UPM');

COMMIT;


SELECT * FROM  ASKTOM_TASKS ;


TSK_CODE             TSK_C TSK
-------------------- ----- ---
CRU-SA-001           PM    CRU
CRU-ML-003           PM    CRU
PRI-CRU-EP-001       EP    CRU
PRI-UPM-OP-001       OP    UPM
UPM-AL-001           PT    UPM
UPM-AL-002           PT    UPM
UPM-WL-001           IN    UPM

-- Create table
create table ASKTOM_CATEGORIES
(
  OBJ_CODE VARCHAR2(30),
  OBJ_DESC VARCHAR2(80)
)
/

insert into asktom_categories values ('CRU', 'computer');
insert into asktom_categories values ('UPM', 'ups system');
insert into asktom_categories values ('VFD', 'drive');

commit;

How can I code the query so that I retrieve the records in the format below? It would not be a problem having a comma between cru-sa-001 and cru-ml-003, etc values inside a column.

These PMs, EPs, etc. cannot be more than 10 and I know what they are.

obj_code   obj_desc     PMs                EPs                     PTs             INs    
        OPs
------------------------------------------------------------------------------------------
-------------------
CRU        computer     CRU-SA-001         PRI-CRU-EP-001
                        CRU-ML-003                                                    
VFD        drive
UPM        ups system                                              UPM-AL-001      
UPM-WL-001     PRI-UPM-OP-001
                                                                   UPM-AL-002

and we said...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336

stragg - to aggregate and concatenate strings

ops$tkyte%ORA10GR2> select obj_code, obj_desc,
  2         stragg( case when tsk_class = 'PM' then  tsk_code end ) pm,
  3         stragg( case when tsk_class = 'EP' then  tsk_code end ) EP,
  4         stragg( case when tsk_class = 'OP' then  tsk_code end ) OP,
  5         stragg( case when tsk_class = 'PT' then  tsk_code end ) PT,
  6         stragg( case when tsk_class = 'IN' then  tsk_code end ) "IN"
  7    from asktom_tasks, asktom_categories
  8   where obj_code = tsk_cat_code
  9   group by obj_code, obj_desc
 10  /

OBJ_C OBJ_DESC   PM         EP         OP         PT         IN
----- ---------- ---------- ---------- ---------- ---------- ----------
CRU   computer   CRU-SA-001 PRI-CRU-EP
                 ,CRU-ML-00 -001
                 3

UPM   ups system                       PRI-UPM-OP UPM-AL-001 UPM-WL-001
                                       -001       ,UPM-AL-00
                                                  2

Reviews

Is this possible in 8.1.7.4 Standard Edition?   March 10, 2008 - 3pm Central time zone Bookmark | Bottom | Top Reviewer: Sinan Topuz from New York Tom, Is this possible in 8.1.7.4 Standard Edition? Thanks Followup   March 10, 2008 - 4pm Central time zone: user defined aggregates (stragg) added to Oracle 5 releases ago, 9ir1 a trick that can use analytics might apply (added to EE in 816, made part of SE in 9i - but not 8i).... but that is not SE. sys_connect_by_path approach to a pivot - added to Oracle 5 releases ago... does not apply. pivot SQL syntax, just added in 11g, does not apply... In 8iR3 SE, I would be hard pressed to do this in SQL. Thanks.   March 10, 2008 - 4pm Central time zone Bookmark | Bottom | Top Reviewer: Sinan Topuz from New York Tom, Knowing that even you will be hard pressed to do this in SQL in (8i SE) relieves me ;-) I think I can live with my current inferior method of listing these records using cursor loops. Thanks

---

Write a Review