Your first line is right. It does depend. There is no single right answer. However, in general, I don't think anyone sets out to be a top expert in certain aspect of SQL Server. They just end up that way. They work with SQL Server and work at getting better at what they work on. Over time, they find out they are especially good at or particularly enjoy a certain feature or group of features, and it grows into a specialty.

For example, when database mirroring was first announced, I didn't set out to master it and eventually write a book on it. My work activities had me working with several different groups that wanted to be early adopters of it. There were a lot of bugs and issues int eh beginning, and very few solutions for dealing with them. It was other become really good with mirroring or not succeed at my job. I became really good out of necessity and through experience because I had to figure out the things that are common knowledge to many people today.