If $\rho=\sum_{i}p_{i}|\psi_{i}\rangle\langle \psi_{i}|$, this ensemble doesn't require $\langle \psi_{i}|\psi_{j}\rangle$=0. Given that $\rho$ is positive semi-definite, by the spectral theorem it can be expressed in diagonal form $$\rho=UDU^{\dagger}, D=\sum_{i}\lambda_{i}|i\rangle\langle i|$$
However, despite having used this notation for a while now, I still find myself confused by the notation of the spectral theorem. Specifically the role the unitaries play. The above states that, $$U^{\dagger}\rho U=U^{\dagger}UDU^{\dagger}U=D$$ which then given $\rho$ in it's diagonal form. However, it can also easily be diagonalised just by calculation of its eigenvalues and eigenvectors, and then re-expression in that basis. Moreover, this just looks like the unitary transformation of $\rho$, which obviosuly isn't going to be the same state. So what are these unitaries then, just the identity operators expanded in the eigenbasis? Or am I meant to interpret them as unitaries whose columns are composed of the states of current basis of $\rho$ expanded in the eigenbasis, ie, not mapping them to their image in another basis, but to themselves expressed in another basis, essentially achieving the same action as the identity?
Edit: It has been pointed out to me that the paragraph wherein I ask my questions is too vague or confusing. So let me try and rephrase. I have a density operator. I have it expressed in matrix form in some basis. I want to change said basis so that it achieves its diagonal form. How would I actually express this as the action of operations on said density operator, given that any such action would just lead to another density operator that is unitarily related, but with a different spectrum? The only way I can see is to rewrite it's entries $|\psi_{i}\rangle = U|\phi_{i}\rangle$ which, to my mind anyway, isn't really the same thing as the action of a unitary operator on $\rho$. To clarify, I am not confused about the action of basis change, but only its representation outside just using the identiy to acieve it, ie $|\psi\rangle=\sum_{i}\langle \phi_{i}|\psi_{i}\rangle |\phi_{i}\rangle$