SQL Server 2008 交叉表查询

Question

我通常可以为我的应用程序找出任何需要的 SQL 查询，但最近遇到了一个需要创建交叉表查询的问题，想知道你是否能帮忙？

我有 3 张表格：

Category(catID, catTitle) 
Equipment(equipID, make, model, quantity, catID, siteID)
Site(siteID, title)

我希望创建一个交叉表查询来显示以下结果集：

Category   Site1   Site2   Site3   Site4   Site5
PC           2       0       10      3      6
Camera       12      4       2       0      8
Printer      3       2       1       1      2

显示的数字表示每个站点中使用“设备”表中数量字段的每个类别项目的总数。我以前从未做过交叉表查询，现在正在努力让它工作。

- tcode

Answer 1

你可以使用“pivot”运算符来实现这个目标。大致如下（尽管我肯定有一些拼写或语法上的错误……）：

select catTitle, [1] as site1, [2] as site2, [3] as site3, [4] as site4, [5] as site5
  from (select category.catTitle, equipment.quantity, site.title
          from equipment
            inner join site
              on (equipment.siteid = site.siteid)
            inner join category
              on (category.catid = equipment.catid)
        ) 
  pivot
  (
  sum (quantity)
    for equipment.siteid in ( [1], [2], [3], [4], [5] )
  ) as pvt
order by pvt.category;

这样做的问题在于您需要知道要包含在查询中的确切站点id集合。如果需要更动态的交叉制表（就像Excel中可以实现的那样），则需要将查询文本生成为字符串，然后使用sp_executesql来运行它。在生成的文本中，您可以根据需要包含尽可能多的“[1]、[2]、[3]、[4]、[5]…”和“[1] as site1，[2] as site2…”等内容。

- Ray

Answer 2

我认为你缺少了一个表格，用于将你的网站和设备进行关联。

类似这样的：

EquipmentSite(SiteID, EquipID)

因为现在不可能确定哪个站点有哪些设备。

编辑：

由于站点ID也包含在设备中，我建议另外创建一张表格，对数据库进行小的重构（因为我真的不知道如何做这个）。

如果你有大量的数据，每次访问这些数据时都需要获取数据并计算，那将会很混乱。

所以我建议创建这张表格。

siteCatCount(CatID, siteID, cnt)

当您修改数据时（添加或删除设备），您需要更新此表，这将更清晰，您无需每次计算每种设备的数量。

- GregM

---

我的道歉。那是一个打字错误，设备表也有一个siteID字段。 - tcode

是的，即使有站点ID，它仍然一团糟 :) - GregM