Personally I'll probably welcome it. So many people drive so crazy (I live in South Florida), the cars will probably be an improvement once they're perfected.
I think the bigger transition already happened 110 years ago or so, when we went from streets that had been full of people and horse-drawn carts since the beginning of civilization, to streets being taken over by Model T's. I think it took only 5 years in some big cities! So self-driving cars are not as big a change for society to introduce, looking at it that way. We're already in the cars. We're just changing who drives them. How folks feel about it, I guess is something else.