SQL Server 2008交叉表查询 [英] Sql Server 2008 Cross Tab Query

问题描述

我通常可以弄清楚我的应用程序所需的任何SQL查询，但是最近我被我需要创建的交叉表查询所困扰，想知道您是否可以提供帮助？

I usually can figure out any sql queries I need for my applications, but I have recently been stumped by a Cross Tab query I need to create and was wondering if you could help?

我有3张桌子

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

我想创建交叉表查询以显示如下所示的结果集

And I would like to create a Cross Tab query to display a result set like below

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

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

The numbers displayed represent a total of each category item within each site using the quantity field withint the Equipment table. I have never had to do a Cross Tab query before and I am struggling to get this working.

推荐答案

您应该能够使用 pivot运算符执行此操作。像这样的东西（尽管我确定我已经弄糊了一些拼写或语法细节...）：

You should be able to do this with the 'pivot' operator. Something like this (though I am sure I muffed some spelling or syntax details...):

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相同的 [1]，[2]，[3]，[4]，[5] ...和 [1]，以及与站点2一样的[2]。 。您需要的东西。

The problem with this is that you need to know the exact set of site ids you want to include in the query. If you need a more dynamic crosstab (like you can get in Excel), then you need to generate the query text as a string and use sp_executesql to run it. In the generated text, you include as many of the "[1], [2], [3], [4], [5]..." and "[1] as site1, [2] as site2..." things as you need.

这篇关于SQL Server 2008交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！