Sql Server 2008 Cross Tab Query

Question

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?

I have 3 tables

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.

Answer 1

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;

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.

Answer 2

I think your missing a table that make a reference between your site and equipment

Something like that :

EquipmentSite(SiteID, EquipID)

Because now it's impossible to tell wich site as wich equipment

EDIT :

Since the siteID is also in equipment I would propose another table, a little refactor of you database (because I really don't know how to do this)

If you get a lot of data it would be a mess to fetch the data and calculate every thing every time you want to access those data.

So I propose this table

siteCatCount(CatID, siteID, cnt)

So when you modify you data (add or remove equipment) you would go update this table, it would be much clearer and you wont have to calculate the count of every equipment each time