SQL Server Pivot用于计数联接表中的实例

Grenville 发表于 Dev

格林维尔

我有3张桌子；类别，位置和业务。

类别和位置表仅具有一个ID和一个名称。

每个业务记录都有一个categoryID，一个locationID和一个名称字段。

我想构建一个表格，以表格形式显示每个位置和类别组合中的商家数量。因此，将类别作为列，将位置作为行，将计数作为单元格数据。

具有总计列和行也将是惊人的。

我知道我应该能够使用数据透视表执行此操作，但是我无法理解数据透视表的语法。

任何帮助将非常感激。

谢谢，

缺口

编辑：这是我的表的JS小提琴；http://sqlfiddle.com/#!2/4d6d2/1

所需的输出：

            | 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

塔林

要获得所需的最终结果，可以使用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 |

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。