Results 1 to 1 of 1

Thread: reverse pivot

  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: reverse pivot

    Hi,

    I'm looking for a single query to flatten:
    Code:
    dealer_id, manager
    1,John Smith
    1,Henry James
    1,Alex Doe
    2,Mark Markson
    2,Jane Janeson
    3,Al Alanson
    into

    Code:
    dealer_id, manager1, manager2, manager3
    1,John Smith, Henry James, Alex Doe
    2,Mark Markson, Jane Janeson
    3,Al Alanson
    1) There is nothing to identify how many managers for a particular dealer_id. (other than the number of rows per dealer)
    2) There is a maximum of 3 managers per dealer.

    I had in mind something like:

    Code:
    SELECT dealer_id, 
    max(case etc..) as manger1,
    max(case etc..) as manager2,
    max(case etc..) as manager3
    from dealer_managers
    group by dealer_id;
    but, because there is nothing to identify the manager number, it throws a wrench in my gears.

    thoughts?
    Last edited by dstachon; 09-24-04 at 19:21. Reason: spelling mistake

Posting Permissions

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