Link to home
Start Free TrialLog in
Avatar of smc1234

asked on

SQL Crosstab Query

I need to create sql cross-tab report in the attached format which will display only the most recent SubCodeDesc for each main Code based on an effective date.
Avatar of urthrilled

SELECT `table`.SubCode, Max(`table`.EffDt)
FROM `table` GROUP BY `table`.SubCode
Avatar of Kevin Cross
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.
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
here's a demonstration of writing a crosstab query for Excel
@urthrilled, Jet's TRANSFORM syntax will not work in SQL Server. You have to use PIVOT and it works differently (as shown above).
Avatar of smc1234


Thanks a lot