SQL Server Pivot for counting instances in join table -

i have 3 tables; category, location , business.

the category , location tables have id, , name.

each business record has categoryid, , locationid, , name field.

i'd construct table shows matrix, number of businesses in each location , category combination. having categories columns , locations rows, counts in cell data.

having totals column , row amazing.

i know should able pivot tables i'm unable head around syntax pivots.

any appreciated.

thanks,

nick

edit: here js fiddle of tables; http://sqlfiddle.com/#!2/4d6d2/1

desired output:

            | activities  | bars     | sweet shops   | total chester     | 1           | 0        | 0             | 1 frodsham    | 0           | 2        | 0             | 2 stockport   | 1           | 0        | 1             | 2 total       | 2           | 2        | 1             | 5 

to final result want can use pivot function. first start subquery returns of data plus gives total of each activity per location:

select l.name location,   c.name category,   count(b.locationid) over(partition b.locationid) total location l left join business b   on l.id = b.locationid left join category c   on b.categoryid = c.id; 

see sql fiddle demo. using windowing function count() over() creates total number of activities each location. once have this, can pivot data convert categories columns:

select    isnull(location, 'total') location,    sum([activities]) activities,    sum([bars]) bars,    sum([sweet shops]) sweetshops,   sum(tot) total (   select l.name location,     c.name category,     count(b.locationid) over(partition b.locationid) tot   location l   left join business b     on l.id = b.locationid   left join category c     on b.categoryid = c.id ) d pivot (   count(category)   category in ([activities], [bars], [sweet shops]) ) piv group grouping sets(location, ()); 

see sql fiddle demo. implemented grouping sets() create final row totals each activity.

the above works great if have limited number of activities if activities unknown, want use dynamic sql:

declare      @cols nvarchar(max),     @colsgroup nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(name)                      dbo.category                     group id, name                     order id             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  select @colsgroup = stuff((select ', sum(' + quotename(name)+ ') '+ quotename(name)                     dbo.category                     group id, name                     order id             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')    set @query = n'select                  isnull(location, ''total'') location, '+ @colsgroup + ', sum(total) total                          (               select l.name location,                 c.name category,                 count(b.locationid) over(partition b.locationid) total               location l               left join business b                 on l.id = b.locationid               left join category c                 on b.categoryid = c.id             ) x             pivot              (                 count(category)                 category in ('+@cols+')             ) p              group grouping sets(location, ());'  exec sp_executesql @query; 

see sql fiddle demo. both versions give result:

|  location | activities | bars | sweet shops | total | |-----------|------------|------|-------------|-------| |   chester |          1 |    0 |           0 |     1 | |  frodsham |          0 |    1 |           0 |     1 | | stockport |          1 |    0 |           1 |     2 | |     total |          2 |    1 |           1 |     4 |