Multiple Rows

Hi, can anyone think of how to get round this. I am using MS Excel Query on my Oracle database.

I want to make a spreadsheet showing account number, customer 1, customer 2, customer 3. There is an accounts table, an accounts_links table which links the customers to the accounts and a customers table.

On the accounts_links table there is one row for each customer on an account with a "holder number" to say if it is customer 1, 2 or 3.

At the moment I am only bringing back accounts where there are 3 customers. Some accounts will only have 1 or 2 customers.

I thought to bring in the accounts_links and customers tables in three times now it is just multiplying all the rows together!

Can anyone think of a way round this to run it in one sql, I don't want to have to use excel functions.

Thanks,
BethIf I've interpreted your issue correctly, here is one suggestion to try:
SELECT a.account_number, MAX(CASE a1.holder_number WHEN 1 THEN c.customer_name END) customer_1,
MAX(CASE a1.holder_number WHEN 2 THEN c.customer_name END) customer_2,
MAX(CASE a1.holder_number WHEN 3 THEN c.customer_name END) customer_3
FROM accounts a, accounts_link al, customer c
WHERE a.a_primary_key = al.al_primary_key
AND a1.al_primary_key = c.c_primary_key
GROUP BY a.account_number

You'll have to edit this to provide the appropriate columns for the WHERE join columns.

Originally posted by elisabeth
Hi, can anyone think of how to get round this. I am using MS Excel Query on my Oracle database.

I want to make a spreadsheet showing account number, customer 1, customer 2, customer 3. There is an accounts table, an accounts_links table which links the customers to the accounts and a customers table.

On the accounts_links table there is one row for each customer on an account with a "holder number" to say if it is customer 1, 2 or 3.

At the moment I am only bringing back accounts where there are 3 customers. Some accounts will only have 1 or 2 customers.

I thought to bring in the accounts_links and customers tables in three times now it is just multiplying all the rows together!

Can anyone think of a way round this to run it in one sql, I don't want to have to use excel functions.

Thanks,
Beth