Results 1 to 3 of 3

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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maximum 3 managers per dealer?

    you're in luck, because ordinarily, the answer to the "flattening" (denormalization) question is to use a scripting language

    this is ugly but i tested it and it works:
    Code:
    select m1.dealer_id
         , m1.manager as manager1
         , m2.manager as manager2
         , m3.manager as manager3
      from yourtable as m1
    left outer
      join yourtable as m2
        on m1.dealer_id
         = m2.dealer_id 
       and m1.manager
         < m2.manager  
       and m2.manager
         = ( select min(manager)
               from yourtable
              where dealer_id = m1.dealer_id 
                and manager > m1.manager )
                   
    left outer
      join yourtable as m3
        on m1.dealer_id
         = m3.dealer_id   
       and m1.manager
         < m2.manager         
       and m2.manager
         < m3.manager                
     where m1.manager
         = ( select min(manager)
               from yourtable
              where dealer_id = m1.dealer_id )
    you would not want to take this more than 3 or 4 levels, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    haha

    very cool.

    thanks very much!

Posting Permissions

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