Results 1 to 2 of 2

Thread: Multiple Rows

  1. #1
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Unanswered: 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,
    Beth
    Beth

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Multiple Rows

    If 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •