reverse pivot

dstachon · #1 (**permalink**) 09-24-04, 18:25

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?

r937 · #2 (**permalink**) 09-24-04, 20:26

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

dstachon · #3 (**permalink**) 09-27-04, 15:14

very cool.

thanks very much!

Internet Services

Web Development

Digital Marketing

Consumer Tech