Home » SQL & PL/SQL » SQL & PL/SQL » [SOLVED] Little help with concat_all
[SOLVED] Little help with concat_all [message #261462] Wed, 22 August 2007 12:01 Go to next message
logidude
Messages: 3
Registered: August 2007
Junior Member
Hi everyone,

I'm kind of in a bind here, trying to get a column concatenated so that multiple rows show on on separated by a delimiter.

I'm using the CONCAT_ALL_8i by Barbara Boehmer:
http://www.orafaq.com/forum/t/67079/0/

This work fine, but in my case I can't get to the colum I want.

My simplified schema is like this

TableA
t1_id (PK)
t1_value

TableB
t2_id (PK)
t2_value
t1_id (FK)
t3_id (FK)

TableC
t3_id (PK)
t3_value

I'm trying to list all records from tableA with the possible multiple records from tableB and tableC in two concatenated columns

Like
TableA_value  ||  TableB_value, TableB_value, ... ||  TableC_value, TableC_value, ...


where TableA.t1_id = TableB.t1_id
and TableB.t3_id = TableC.t3_id

Hope this make sense, I can get the TableB value fine, but having lot of trouble getting to the tableC values...I can get the ID just fine as they are linked to my original record.

Don't hesitate to ask if you need precision. Thanks a lot for your help!

[Updated on: Thu, 23 August 2007 07:46]

Report message to a moderator

Re: Little help with concat_all [message #261464 is a reply to message #261462] Wed, 22 August 2007 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table, insert statements and the expected result with these ones.
This will help us to find how to do it and thus help you.

Regards
Michel
Re: Little help with concat_all [message #261465 is a reply to message #261462] Wed, 22 August 2007 12:12 Go to previous messageGo to next message
logidude
Messages: 3
Registered: August 2007
Junior Member
Hi Michel,

I'll work on a test case this evening from home as I would still like to know the best way to do it.

For the moment I've found a work around by creating a view with the information I needed for the 3rd column.

Thanks for the reply.
Re: Little help with concat_all [message #261631 is a reply to message #261465] Thu, 23 August 2007 03:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do it bit by bit.

To get just the TableB concatenated values:
SELECT t1_value, concat_all(concat_expr(t2_value, ', ')) AS t2_values
FROM   tableA a
JOIN   tableB b ON B.t1_id = A.t1_id
GROUP BY t1_value;


To get just the TableC concatenated values:
SELECT t1_value, concat_all(concat_expr(t3_value, ', ')) AS t3_values
FROM   tableA a
JOIN   tableB b ON B.t1_id = A.t1_id
JOIN   tableC c ON C.t3_id = B.t3_id
GROUP BY t1_value;


Try these separately to make sure the concatenated string are as desired. Add a DISTINCT if you are getting duplicates on the 2nd sql that you dont want.

Now join them together:
SELECT a.t1_value, t2_values, t3_values
FROM ( .. 1st SQL .. ) x
JOIN ( .. 2nd SQL .. ) y ON X.t1_value = Y.t1_value


Ross Leishman
Re: Little help with concat_all [message #261727 is a reply to message #261631] Thu, 23 August 2007 07:46 Go to previous message
logidude
Messages: 3
Registered: August 2007
Junior Member
Hi rleishman,

Thanks for the reply, this worked great when I adapted it to the concat_all_8i function. I couldn't figure out my joins properly.

Thanks again, have a good day.
Previous Topic: Moving data from one tablespace to other
Next Topic: Instead of using DISTINCT is there any other way to easy filter the data's + How can i make Fast
Goto Forum:
  


Current Time: Sat Dec 03 06:14:05 CST 2016

Total time taken to generate the page: 0.11119 seconds