I have actually used APPLY (er, perhaps twice!) but not PIVOT. Just doesn't feature in the type of work I do.

A good grasp of the fundamentals is always important, but coding in a clear, maintainable style is just as crucial.

Communication skills work both ways - comprehending the input spec, and creating stuff that somebody else can pick up and run with are equally important.

If I was recruiting somebody for our business, I'd also be very interested in people who understand locking and blocking (ours is a real-time, response-time-sensitive application) and Service Broker (we use it extensively) but I appreciate that isn't everyone's cup of tea.