joining multiple rows from a single table

There is a database table used to store financial analyltic information by source (vendor) and date. Rows in this table are sparsely populated, based on data availabilitiy from a given vendor.

Source. date...... a1.. a2. a3..
====== ======= === === ====
sourceA 20050621 12.3 40.2 546
sourceB 20050621 00.0 39.8 545


At run time, I recognize the preferred source for each of the analytic fields (columns), and want to create a query that returns a single result with column values from more than 1 row. For example, the I may want the result set to include the a1 value associated with sourceA and the a2 and a3 values associated with sourceB, so that the result set is as follows

12.3 39.8 234

How would I create a SQL query to fetch these values and return this result?

If you know exactly which cols you need from which rows, you can try:



This strikes me as a very messy way to do this, though -- depending on the specifics of your data and how you will determine which row's columns you need, there are probably more reliable ways.

Thanks for the information ... I was concerned there was no standard SQL method of performing the query, and now I realize I need to educate myslef a bit more on inner joins :-)