sql server - SQL over partition count then group by month -

i've been playing around using count(value) on (partition anothervalue) , love how fast pulling results db. i'm trying step things can create table show me multiple counts - 1 each month, side side. i'm not sure if possible wanted check here. had around online couldn't find examples of question having been asked before, no leads.

here have:

select distinct tbl.category 'category name', count(tbl.row_id) on (partition tbl.category) 'rows in category' mydb.tbl tbl.field1 = 'something' , tbl.field2 = 'somethingelse' , tbl.created >= '2014-01-01' order [rows in category] desc 

gives me lovely table this:

category name |rows in category  abc          | 166  cba          | 137  ccc          | 112 

where i'm trying subdivide month output ends looking (does not have exact, headers can shuffled around):

january                          |february  category name |rows in category | category name |rows in category    abc          | 162             |  cba          | 51   cba          | 86              |  ccc          | 32                  ccc          | 70              |  abc          | 4 

when try adding group by throws error not being contained in aggregate function.

if have can stack queries on top of each other , limit each 1 show 1 month, seems lot of repetitive code , i'd prefer side side view if can done.

any ideas?

the format not described. normal way show result , information has been included, make sure not display more 1 year @ time.

try this:

;with x (    select tbl.category, tbl.row_id, datename(month, tbl.created) month          mydb.tbl          tbl.field1 = 'something'       , tbl.field2 = 'somethingelse'       , tbl.created >= '2014-01-01'       , tbl.created < '2015-01-01' ) select category 'category name', [january],[february] x    pivot(       count([row_id])         month       in([january],[february])      )as p