Tricky Import Situation

[johnmsch]

I have some data that I'd like to put in a spreadsheet, and can't figure out the trick to doing this. Here is how the table in SQL Server looks, but of course I could export this to a flat file, or a smiple spreadsheet

name year month count
----- ---- ----- -----
aaa 10 6 555
bbb 10 5 22
aaa 11 2 345
ccc 10 1 44


There are actually about 350+ rows in this table. Roughly 35 unique names, and the dates will span from January of 2010 to March of 2012.

Here is how I'm hoping to get the spreadsheet to look. Of course, I would manually edit the headers once the data is in place:



------------------------------- 2010 -------------------------------- ---------2011 (etc)
Name Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar (etc)
aaa 555 345
bbb 22
ccc 44


There will be one unique entry for each of the buckets.

Thanks for any help with this!

[rylo]

Hi

Bring it into a spreadsheet, then use a pivot table with name as the row, year and month as the column, and count as the value.

This will at least make it into a matrix format for you.

HTH

rylo

[johnmsch]

Sorry rylo I never thanked you for the tip. Found myself needing to do this again and found this old thread.

Got everything in a matrix, now just need to clean things up. Very impressed at what can be done with pivot tables, but I've got a LOT to learn!!!!

Thanks again

[johnmsch]

I finally have almost everything looking the way I want it, except for the column headers.

Based on the data as shown in my first post, I have the pivot table with the "name" field as the rows, then grouped by year and month as columns. What is the trick to get the month columns to show as a 3 character value (JAN, FEB, MAR...) instead of just the numbers (1, 2, 3...)? Been playing with this for a while, and still can't figure this out.

BTW, now that I know more about pivot tables, this thread title could be changed to "Basic Import Situation"!

[johnmsch]

Any tips on how I can get the month numbers to show as a 3 character label? Here's how it looks now:pivotheader.jpg

I'd really like to have JAN, FEB, MAR, etc in those column headers instead of the 1, 2, 3...

[protonLeah]

deleted by bvj