OraFAQ Forum: SQL & PL/SQL » [SOLVED] Little help with concat

Home » SQL & PL/SQL » SQL & PL/SQL » [SOLVED] Little help with concat_all

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

[SOLVED] Little help with concat_all [message #261462]

Wed, 22 August 2007 12:01

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

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Little help with concat_all [message #261465 is a reply to message #261462]

Wed, 22 August 2007 12:12

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.

Report message to a moderator

Re: Little help with concat_all [message #261631 is a reply to message #261465]

Thu, 23 August 2007 03:04

rleishman
Messages: 3728
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

Report message to a moderator

Re: Little help with concat_all [message #261727 is a reply to message #261631]

Thu, 23 August 2007 07:46

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 21:23:01 CST 2024