SQL Crosstab Query

SELECT `table`.SubCode, Max(`table`.EffDt)
FROM `table` GROUP BY `table`.SubCode

GROUP BY approach might work, but you will have to do a conditional aggregate using CASE. For example, MAX(CASE MainCode WHEN '11111' THEN EffDt END) AS MainCode_11111 will get you the most recent effective date for a given SubCode grouping.

EDIT: I remember now why I was going to suggest PIVOT, you want the SubCodeDesc as the value and not the date. Therefore, I would write your data selection subquery with ROW_NUMBER() windowing function and OVER() analytic clause to rank rows by MainCode-SubCode combinations. When you PIVOT rows with rank = 1, you will get the most recent values of SubCodeDesc.

here's a demonstration of writing a crosstab query for Excel

http://datapigtechnologies.com/blog/index.php/running-crosstab-queries-in-excel/

@urthrilled, Jet's TRANSFORM syntax will not work in SQL Server. You have to use PIVOT and it works differently (as shown above).

Thanks a lot