question

First off, I'd like to suggest that the structure of the `Commdown` table might be changed a little bit in a way that would dramatically simplify this type of query. Because columns `rateA` through `rateZ` all represent rates, and are presumably the same data type, you could get rid of them entirely and replace them with a `Mas90Letter` column and a `rate` column. This would give you 26 rows per `empID`/`downID` containing rate information that you could `JOIN` against (`ON Commdown.Mas90Letter = creditType.Mas90Letter`) rather than having to dynamically pick a rate column based on the value of `Mas90Letter` in the `credittype` table. Of course, it's not usually possible to change one's table structure based on some random internet forum suggestion, so a (hopefully good) alternative to actually conditionally picking a column is to logically turn the columns into rows at query time with the `UNPIVOT` operator, then do the `JOIN` I alluded to earlier. I'll place mine in a CTE and alias the rate columns with the appropriate `Mas90Letter` for ease of `JOIN`ing, e.g., `rateA as A`: ;with unpivotedCommdown as ( select commdownID, empID, downID, CAST(rateType as CHAR(1)) as rateType, rate from( select commdownID, empID, downID, rateA as A, rateB as B, rateZ as Z from #Commdown) as base unpivot(rate for rateType in ([A], [B], [Z])) as source ) select o.orderID, o.empID, o.commdate, cType.Mas90Letter, c.rateType, c.rate from #orders as o inner join unpivotedCommdown as c on o.empID = c.empID inner join #creditType as cType on o.creditTypeID = cType.creditTypeID AND c.rateType = cType.Mas90Letter where o.commdate = '20101116'; Just supply the appropriate `commdate` for the `WHERE` clause. My results looked like this: orderID empID commdate Mas90Letter rateType rate ----------- ----------- ---------- ----------- -------- ------- 1 413 2010-11-15 A A 0.10250 1 413 2010-11-15 A A 0.09125 1 413 2010-11-15 A A 0.08875 Is it close to what you were after? The data you supplied were a bit sparse, so I improvised a bit with the data in the my sample tables. Speaking of sample tables, here are the statements to create and populate the sample tables I used to make the above query work. As I mentioned, I had to guess at how some of the data might look. create table #orders ( orderID int, empID int, creditTypeID int, commDate date); insert #orders values(1,413,50,'20101115'); insert #orders values(2,413,51,'20101116'); insert #orders values(3,413,50,'20101117'); insert #orders values(4,413,51,'20101118'); create table #creditType ( creditTypeID int, minFico int, maxFico int, Mas90Letter char(1)); insert #creditType values(50, 600, 605, 'A'); insert #creditType values(51, 590, 599, 'B'); create table #Commdown ( commdownID int, empID int, downID int, rateA numeric(6,5), rateB numeric(6,5), rateZ numeric(6,5)); insert #Commdown values(1, 413, 459, 0.10250, 0.09125, 0.08875); insert #Commdown values(2, 413, 459, 0.09125, 0.08875, 0.08375); insert #Commdown values(3, 413, 459, 0.08875, 0.08250, 0.07900); Once the tables are created and populated, and the query is run, we can do a little cleanup (or just end your SSMS session): drop table #orders; drop table #creditType; drop table #Commdown; Table scripts, sample data and desired output are the types of information an asker can supply to turn a vague question into a great question. It's a courtesy to show that the asker is invested in the question and the outcome, and it gives people who might answer a huge leap on understanding the problem and getting a working solution.

Ken, Thanks for the reply. I am working on SQL server 2000 and just realized, PIVOT and UNPIVOT is not supported in 2000. I cannot change the structure of Commdown table, since its a production box. Did a little research on google and found can use union to achieve the same. select commdownID,empID, downID, 'A' as ratetype, rateA as rate from #Commdown union select commdownID,empID, downID, 'B' as ratetype, rateB as rate from #Commdown union select commdownID,empID, downID, 'z' as ratetype, ratez as rate from #Commdown Its 26 unions A thro Z, but works for me. Thanks for the idea. Regards SQLNewbie