This blog entry does two things - first, it shows a view that brinks in 401K detail for a company. The view (obviously) could be altered to bring in different pay codes.
Second, it shows a cool CTE technique that can be modified to work for any view; and it's worth reading the code just to see how it's done.
We talk multiple data sets in a CTE, all comma separated, and then merge them into the final output.
Normally I'd use temp tables, but that's not available in the simple view format (but would be in the more complicated view format)