Collapsing results to a single row.

[stiley]

Hello,
Question about formatting my query results.

I have a customer table and a customer_phone table linked by customer_id field.

When I select records from customer_phone for a particular customer_id I get as many rows as the customer has phone numbers.

For example

select * from customer_phone where customer_id = 123;

Yeilds

Customer_Id Phone
123 123-345-4567
123 123-345-9987
123 123-345-2232


What I would like is to be able to collapse that to one result somehow but I do not have a clue.

so something like

Customer_Id Phone1 Phone2 Phone3
123 123-345-4567 123-345-9987 123-345-2232




Any thoughts would be appreciated.

 

[r937]

my first thought is, this shouldn't have been posted in the ANSI SQL forum

could you mention which database system you're using, please?

also, why couldn't you do the crosstab layout (as that's what it's called) in your application language?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[stiley]

Apologies.
Using Oracle 10g

Not actually coding, just writing a query to find data so just using pure SQL. Found this link that gives me what I'm looking for.

http://thinkoracle.blogspot.com/2005/09/pivot-and-crosstab-queries.html

Thanks

 

[stiley]

Here is the solution...

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php#wm_concat

Sean

 

[r937]

whoa, LISTAGG... first i've heard of that

nice one



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon