formatting data in sql

[je27]

I have a query that is looking in many different tables and I am trying to see if there is a way to retrieve the data below so that it removes duplicates but puts the values that are not dupilicate in the same cell with a comma delimiter.
Tables:
Name.......Code....Well....Project
ST10959....16027...H1......one
ST10950....16027...A2......two
KB106832...5410....B2......three
KB106832...15678...B2......three
KB106832...13069...B2......three
U15527.....15410...C2......four
U15527.....15768...C2......four
KB106832...5410....D2......five
KB106832...15678...D2......five

This is the way the table looks (see above)
there are more than one codes for each project.
I want to return the codes but without all the information.

Is there anyway in sql to return the table like this or something similar to this:

Name.......Code....................Well...Project
ST10959....16027...................H1.....one
ST10950....16027...................A2.....two
KB106832...5410,15678,13069...B2.....three
U15527.....15410,15768...........C2......four
KB106832...5410,15678............D2......five

 

[SantaMufasa]

JE27,

Here is a sample of how you can achive what you want:

create or replace function str_cde
    (n_in varchar2, w_in varchar2, p_in varchar2)
    return varchar2 is
    hold_cds varchar2(500);
begin
    for c in (select cd from je27
               where n_in = Name
                 and w_in = Well
                 and p_in = Project) loop
        hold_cds := hold_cds||','||c.cd;
    end loop;
    return ltrim(hold_cds,',');
end;
/

Function created.

col code format a20
select distinct name
      ,str_cds(name,well,project) Code
      ,Well
      ,Project
from je27;

NAME       CODE                 WELL       PROJECT
---------- -------------------- ---------- -------
KB106832   5410,15678           D2         five
KB106832   5410,15678,13069     B2         three
ST10950    16027                A2         two
ST10959    16027                H1         one
U15527     15410,15768          C2         four

5 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[je27]

Hi,
So this function is used after the data is retrieved, right?

So if i have an application I can use a function like this to manipulate the data and output it this way in excel?

 

[je27]

OH and Thank you!!!

 

[SantaMufasa]

JE,

The function executes as Oracle retrieves the data. The SELECT in the function's LOOP executes independently of the outer, calling SELECT.

if i have an application I can use a function like this to manipulate the data and output it this way in excel?

I'm not quite sure what you are asking...Are you asking, "Can this function run in Excel?" (Ans. No, Excel cannot run PL/SQL functions.); or are you asking, "Can I produce a query that uses this function to assist in outputting flat ASCII data for Excel to use?" (Ans. Yes)


Let us know your follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[je27]

Sorry, for the misunderstanding. Ideally I would like to be able to run a sql to output the data the way you have shown. I just wasn't sure how the function worked, but you have clarified that now. I simply have to run the create function sql then incorporate:
col code format a20
select distinct name
,str_cds(name,well,project) Code
,Well
,Project
from je27;
into my sql that I currently have?

 

[SantaMufasa]

Exactly !

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[je27]

So my next question for you is how?
I have a very large sql statement that is returning many more columns than those 4. I know that I would have to change the function a bit to include those columns but I'm stuck on what I would change "je27" to. For instance in the function:
for c in (select cd from je27
where n_in = Name

and then also in the select statement:
,Project
from je27;

I can show you what my query looks like if that would help?

Thanks,
JE

 

[SantaMufasa]

JE,

Replace "JE27" with whatever is the real name of the table that contains the code values. Additionally, the arguments that you pass to the function need only be the value(s) that are necessary to "group" the multiple rows down into a single row that adequately represents the "multi-code" values.

I hope that helps to explain the adjustments that you need to make. If not, we can always adjust the explanation.<smile>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[je27]

Well the table that has the codes doesn't have all the other columns. This is my sql:

SELECT lot_warehouse.molname as Compound_Name,lot_warehouse.lnb_ref as LNB_Ref,sts_project.project_code as P_Codes,
Decode(Well.WELL_ROW,1,'A',2,'B',3,'C',4,'D',5,'E',6,'F',7,'G',8,'H',9,'I',10,'J',11,'K',12,'L',13,'M',14,'N',15,'O',16,'P')||to_char(Well.WELL_COLUMN) as Location,container_master.barcode,
storage_unit.stor_unit_name as Request_Type, sts_protocol.protocol_name as Request, to_char(sample.current_amount)||' '||sample.uom as Amt_Info, to_char(sample.sample_concentration)||' '||sample.conc_uom as Conc_Info
FROM container_master,plate,well,sample,lot_warehouse,storage_unit,sts_plate_creation_order, sts_order_output_container,sts_protocol, sts_lot_project_map, sts_project
WHERE container_master.container_id=plate.container_id and plate.container_id=well.container_id and well.sample_id=sample.sample_id(+) and sample.lot_id=lot_warehouse.lot_id
and lot_warehouse.lot_id = sts_lot_project_map.lot_id and sts_lot_project_map.project_id = sts_project.project_id and sts_project.project_code not in('3861','15867','7196','5930','4890','4909','3703')
and container_master.storage_unit_id=storage_unit.stor_unit_id and container_master.container_id=sts_order_output_container.container_id and sts_order_output_container.order_id=sts_plate_creation_order.order_id
and sts_plate_creation_order.protocol_id=sts_protocol.protocol_id and container_master.barcode in (*)
ORDER BY container_master.barcode,well.well_column,well.well_row

 

[je27]

I wouldn't know where to put that select statement or how to create the function from just one table?

Sorry if I'm unclear.

Thanks,
JE