Multi row string concatenation 1

[pdelpy]

I am currently struggling with a particularly nasty old DB schema and query set, if anyone can help with resolving the below problem i would be very grateful. The example below is a simplified (Non real) version of the problem.

Essentially i have two tables (both fairly normalized) which need to be joined in a particular manner:

table 1: People
PID Name
1 Peter
2 John
3 Dave

Table 2: Clothing
PID Item_name
1 Gloves
1 Hat
1 Overcoat
2 Socks
3 scarf
2 shoes

The end result needs to look like the following:
Name PID Clothing
Peter 1 Gloves, Hat, Overcoat
John 2 Socks, shoes
Dave 3 Scarf

Although the join is easy, the concatenation of the strings (clothing) is the part with which i am struggling (BTW I'm not worried about the comma separator, as long as there is any separator i can work with it). The clothing table may have 0 up to infinity records in it per person.

I know that there is a solution in Sybase (the LIST function) but i am stuck with ORACLE.

Once again my thanks in advance
Peter Delpy

 

[MikeJones]

I can't think of a way to do it in straight SQL, you could however write a Stored Function called has_clothes, which for a name and/or PID returns a comma seperated list of the clothes they have. As you could write the function in PL/SQL, you'd have alot more power.

Your sql statement would then look like

select name, pid, has_clothes(pid)
from ....
where....

HTH,

Mike.

 

[carp]

And the function might look something like:

CREATE OR REPLACE FUNCTION has_clothes(p_pid NUMBER) RETURN VARCHAR2 AS

v_string VARCHAR2(2000) := NULL;
CURSOR clothes_cursor IS
SELECT item_name
FROM clothing
WHERE pid = p_pid;

BEGIN
FOR c IN clothes_cursor LOOP
IF (clothes_cursor%ROWCOUNT > 1) THEN
v_string := v_string||', '||c.item_name;
ELSE
v_string := c.item_name;
END IF;
END LOOP;
RETURN v_string;
END;

 

[MikeJones]

I'd say that would do it!!

BTW,

I keep seeing references to PRAGMA RESTRICT_REFERENCES so that functions can be used in queries, I know that you can not call a function that will do any DML. However I have lots of functions that neither have DML or the RESTRICT_REFERNECES and I can still call them from sql, Does anyone know under what circumstances you need to use the RESTRICT_REFERENCES? The Oracle documentation only confusses me...


Cheers,

Mike.

 

[carp]

Mike -
The only time I have run afoul of RESTIRCT_REFERENCES has been when I've put the function into a package and tried to use it. However, I still haven't come up with a good way to explain why having the function in a package forces this distinction.

 

[MikeJones]

Carp,

Hmmm, we only use packages, so all of these functions I'm talking about are held within a package. I've never had to use RESTRICT_REFERENCES yet, I guess I'll just have to remember to make it the first thing I try if I run into trouble

Cheers,

Mike.

 

[krkX]

Hi there, there's no easy way to do so, BUT it can be done...

You should try using a variable an begin and go statements,..

Also depending why you need it to be like this, if it's just for reporting you can use crystal reports...

I havent really got the time to fix this but i'll give it a go...

krkX