Link to home
Start Free TrialLog in
Avatar of snooflehammer
snooflehammerFlag for Australia

asked on

Getting a crosstab query to display month columns as names in the correct order

I'm querying a table of data that contains a date, consultant, client and $ value.

I want to summarise it in a table with the first column the consultant, the second the client. The remaining columns are the months. The$ value (QuoteAmount) is the value that populates the table of results.

SQL so far:

TRANSFORM Sum(qryBudgetReportBase.QuoteAmount) AS SumOfQuoteAmount
SELECT qryBudgetReportBase.Consultant, qryBudgetReportBase.ClientName
FROM qryBudgetReportBase
GROUP BY qryBudgetReportBase.Consultant, qryBudgetReportBase.ClientName
ORDER BY qryBudgetReportBase.Consultant, qryBudgetReportBase.ClientName, Month([StartDate])
PIVOT Month([StartDate]);

I've attached a shot of the results without the first two columns because they are identifiable.

Problems:

The column headers are month numbers. I need month names.

Months with no data do not appear. I need empty months.

The months are in Jan-Dec order, I need them in the order of the months from the start of the dataset, in this case July, so they should read left to right from Jul-Jun
Crosstab.JPG
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try

PIVOT Format([StartDate],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun")

but i suggest you need to add the year in your header i.e.,  2014 Jul, 2014 Aug ....
Will the dataset always start from July or does this change?
Avatar of snooflehammer

ASKER

Always from July, but I'll need to specify the year
Ignore the year specification. I've already isolated the date range in the subset of data I'm working with
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had to modify it slightly, but it essentially did the job:

TRANSFORM Sum(qryBudgetReportBase.QuoteAmount) AS SumOfQuoteAmount
SELECT qryBudgetReportBase.Consultant, qryBudgetReportBase.ClientName
FROM qryBudgetReportBase
GROUP BY qryBudgetReportBase.Consultant, qryBudgetReportBase.ClientName
ORDER BY qryBudgetReportBase.Consultant, qryBudgetReportBase.ClientName
PIVOT Format([StartDate],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun");

Thank you