如何编写数据透视表以将每年的记录显示为列？

Question

我很确定我需要一个支点来做到这一点，但无法完全弄清楚。 （sql newb）

我有这样的数据：

ID    CompanyID    Year    Revenue    Expenses
1     0003         2011    12000      4000
2     0003         2010    9000       6000
3     0003         2009    7000       9000
4     0010         2011    134300     34000
5     0010         2010    43000      46000
6     0010         2009    73000      39000

我可以使用数据透视来显示这个表，如下所示：

CompanyID    2011-Revenue    2010-Revenue    2009-Revenue    2011-Expenses   2010-Expenses    2009-Expenses
0003         12000           9000            7000            4000            6000             9000
0010         134300          43000           73000           34000           46000            39000

这是我到目前为止所拥有的...

SELECT P1.*
FROM    (SELECT [CompanyID]
            ,CASE P.[Year] WHEN 2011 THEN P.[Revenue] ELSE NULL END AS '2011-Revenue'
            ,CASE P.[Year] WHEN 2010 THEN P.[Revenue] ELSE NULL END AS '2010-Revenue'
    FROM tblRecords P WHERE P.[CompanyID] = @companyID GROUP BY CompanyID, [Year], [Revenue]) AS P1

正在返回：

CompanyID    2011-Revenue    2010-Revenue 
0003         12000           NULL
0003         NULL            9000

我的结果几乎没有问题...

1. 有CompanyID 0003 的两条记录我希望将其分组为一条记录

2. 我一次只能选择 1 个公司，我需要选择多个。我试过了

   <前><代码>来自 tblRecords P 其中 P.[CompanyID] IN (@CompanyIDs) GROUP BY CompanyID、[年份]、[收入]) AS P1

   其中 @CompanyIDs 是一个类似于 '0003, 0010' 的字符串 - 它没有失败，但结果只是一个带有标题且没有数据的空表。

任何帮助将不胜感激..或者让我知道我是否误解了枢轴？

多谢！

Thomas

编辑：使用 Microsoft SQL Server Management Studio 2005 Express

更新 2：我已经想出了连接表的更多详细信息，但是我仍然需要能够将 CompanyID 作为逗号分隔的字符串传递..对此的任何帮助都是赞赏。

vvvvvvvv 我已经在下面弄清楚了（一旦一切正常，就会发布）vvvvvvv

更新：看起来鲁本提出的方案将会起作用，但是我刚刚确定我需要更多的功能......我可以加入吗与另一个表一起使用标题

  CompanyID    CompanyName    CompanyAddress    2011-Revenue    2010-Revenue 

其中 CompanyName 和 CompanyAddress 来自另一个表（tblCompanyDetails）

我尝试使用：

SELECT *
FROM 
(
    SELECT  CompanyID, tblCompanyDetails.CompanyName, tblCompanyDetails.CompanyAddress, CAST(YEAR AS varchar) + ' - Revenue' Type,
            Revenue Value FROM tblRecords
    FROM tblRecords INNER JOIN tblCompanyDetails ON tblRecords.CompanyID = tblCompanyDetails.CompanyID
    UNION ALL
    SELECT  CompanyID, tblCompanyDetails.CompanyName, tblCompanyDetails.CompanyAddress, CAST(YEAR AS varchar) + ' - Expenses' Type,
            Expenses  Value FROM tblRecords 
    FROM tblRecords INNER JOIN tblCompanyDetails ON tblRecords.CompanyID = tblCompanyDetails.CompanyID
) src
    PIVOT
(
    SUM(Value) for [Type] in
([2011 - Revenue], [2010 - Revenue], [2009 - Revenue],
     [2011 - Expenses], [2010 - Expenses], [2009 - Expenses]
)
) pvt
WHERE CompanyID = @CompanyID

我收到错误：

Msg 1038, Level 15, State 4, Procedure spCompare, Line 10
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. 

Answer 1

试试这个：

DECLARE @CompanyIDs XML
DECLARE @Records  TABLE
(
    ID int,
    CompanyID char(4),
    Year int,
    Revenue decimal,
    Expenses decimal
)
DECLARE @CompanyDetails TABLE
(
    CompanyID char(4),
    Name varchar(50),
    Address varchar(50)
)

SET @CompanyIDs = '
<filter>
    <CompanyID>0003</CompanyID>
    <CompanyID>0010</CompanyID>
</filter>'

INSERT INTO @Records 
(ID, CompanyID, Year, Revenue, Expenses)VALUES 
(1, '0003', 2011, 12000 , 4000 ),
(2, '0003', 2010, 9000  , 6000 ),
(3, '0003', 2009, 7000  , 9000 ),
(4, '0010', 2011, 134300, 34000),
(5, '0010', 2010, 43000 , 46000),
(6, '0010', 2009, 73000 , 39000)

INSERT INTO @CompanyDetails 
(CompanyID, Name, Address) VALUES
('0003', 'Company A', 'A Street'),
('0010', 'Company B', 'B Street')

SELECT *
FROM 
(
    SELECT  CompanyID, CAST(YEAR AS varchar) + ' - Revenue' Type,
            Revenue Value FROM @Records 
    UNION ALL
    SELECT  CompanyID, CAST(YEAR AS varchar) + ' - Expenses' Type,
            Expenses  Value FROM @Records 
) src
PIVOT
(
    SUM(Value) for [Type] in
    ([2011 - Revenue], [2010 - Revenue], [2009 - Revenue],
     [2011 - Expenses], [2010 - Expenses], [2009 - Expenses]
    )
) pvt
JOIN    @CompanyDetails Details
    ON  Details.CompanyId = pvt.CompanyID
WHERE   pvt.CompanyID IN
(
    SELECT  T.C.value('.', 'char(4)')
    FROM    @CompanyIDs.nodes('/filter/CompanyID') T(C)
)

您需要将您的公司过滤器作为 XML 发送，并使用该子选择来根据数据透视操作的需要来中断数据。对于 JOIN 操作，只需使用 pvt 输出