SQL Server 2000数据透视查询

Question

如果之前已经回答过这个问题，请原谅我。

我正在寻找有关如何构建数据透视表或交叉表查询的一些建议。

我有一个看起来像这样的表格，

Vessel    Date    Inspector
 A      02/05/10    Morris
 B      05/20/10   Clouseau
 A      07/25/10    Gadget

我需要结果看起来像这样

Vessel  Jan   Feb      Mar     April   May   June   July   Aug   Sept   Oct    Nov  Dec
  A           Morris                                Gadget
  B                                  Clouseau

希望这是有道理的。我希望有人能给我一些建议或帮助我开始这方面的工作。

谢谢

Answer 1

Select Vessel
    , Min ( Case When Month([Date]) = 1 Then Inspector End ) As Jan
    , Min ( Case When Month([Date]) = 2 Then Inspector End ) As Feb
    , Min ( Case When Month([Date]) = 3 Then Inspector End ) As Mar
    ...
    , Min ( Case When Month([Date]) = 12 Then Inspector End ) As Dec
From Table
Group By Vessel

您必须静态声明列。应该注意的是，这仅适用于一年。如果您正在寻找的是动态列（月）生成，那么尝试在 T-SQL 中执行此操作并不是正确的方法，因为您只能使用一些丑陋的动态 SQL 来执行此操作。相反，您应该使用报告生成器或中间层组件来构建结果集。

Answer 2

我认为您不需要为此使用 PIVOT - 只需创建 12 个子查询，每个月一个：

SELECT
    Vessel,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=1) as Jan,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=2) as Feb,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=3) as Mar,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=4) as Apr,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=5) as May,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=6) as Jun,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=7) as Jul,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=8) as Aug,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=9) as Sep,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=10) as Oct,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=11) as Nov,
    (SELECT TOP 1 Inspector FROM Stuff WHERE Vessel=Tbl.Vessel AND MONTH(Date)=12) as Dec
FROM (
    SELECT DISTINCT(Vessel) FROM Stuff
) Tbl

您可以通过添加 AND YEAR(Date)=2010 （对于示例）到 12 个子查询中每个子查询的末尾。