SQL Server Pivot для подсчета экземпляров в таблице соединений

Чтобы получить окончательный результат, который вы хотите, вы можете использовать функцию PIVOT. Сначала я бы начал с подзапроса, который возвращает все ваши данные, а также дает вам общее количество действий для каждого местоположения:

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

См. SQL Fiddle с демонстрацией. Использование оконной функции count() over() создает общее количество действий для каждого местоположения. Получив это, вы можете повернуть данные, чтобы преобразовать ваши категории в столбцы:

select 
  isnull(location, 'Total') Location, 
  sum([Activities]) Activities, 
  sum([Bars]) bars, 
  sum([Sweet Shops]) SweetShops,
  sum(tot) total
from
(
  select l.name location,
    c.name category,
    count(b.locationid) over(partition by b.locationid) tot
  from location l
  left join business b
    on l.id = b.locationid
  left join category c
    on b.categoryid = c.id
) d
pivot
(
  count(category)
  for category in ([Activities], [Bars], [Sweet Shops])
) piv
group by grouping sets(location, ());

См. SQL Fiddle с демонстрацией. Я также реализовал GROUPING SETS(), чтобы создать последнюю строку с итоговыми данными для каждого действия.

Вышеприведенное прекрасно работает, если у вас ограниченное количество действий, но если ваши действия будут неизвестны, вам нужно будет использовать динамический SQL:

DECLARE 
    @cols AS NVARCHAR(MAX),
    @colsgroup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(name) 
                    from dbo.category
                    group by id, name
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsgroup = STUFF((SELECT ', sum(' + QUOTENAME(name)+ ') as '+ QUOTENAME(name)
                    from dbo.category
                    group by id, name
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = N'SELECT 
                Isnull(location, ''Total'') Location, '+ @colsgroup + ', sum(Total) as Total
            from 
            (
              select l.name location,
                c.name category,
                count(b.locationid) over(partition by b.locationid) total
              from location l
              left join business b
                on l.id = b.locationid
              left join category c
                on b.categoryid = c.id
            ) x
            pivot 
            (
                count(category)
                for category in ('+@cols+')
            ) p 
            group by grouping sets(location, ());'

exec sp_executesql @query;

См. SQL Fiddle с демонстрацией. Обе версии дают результат:

|  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 |