首页 文章

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

提问于
浏览 次
1

我很确定我需要一个支点才能做到这一点，但无法弄明白 . （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

我可以使用Pivot来显示此表，如下所示：

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

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

  • CompanyID 0003有两条记录我想把它分成一条记录

  • 我一次只能选择1家公司，我需要选择多家公司 . 我试过了

FROM tblRecords P
WHERE P.[CompanyID] IN (@CompanyIDs)
GROUP BY CompanyID, [Year], [Revenue]) AS P1

其中 @CompanyIDs 是一个像 '0003, 0010' 这样的字符串 - 它没有失败，但结果只是一个带有 Headers 但没有数据的空表 .

任何帮助将不胜感激..或让我知道，如果我误解枢轴？

非常感谢！

托马斯

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

更新2：我已经想出了加入表格以获取更多细节，但我仍然需要能够将公司ID作为逗号分隔的字符串传递..任何有关的帮助将不胜感激 .

vvvvvvvv我已经将其显示出来（将在所有工作后发布）vvvvvvv

更新：看起来像Ruben提出的将会起作用，但我刚刚确定我需要更多的功能...我可以加入另一个表来获得 Headers

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.
sql-server sql-server-2005 tsql pivot crosstab

1 回答

  • 2

    试试这个：

    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 输出

    回复于

相关问题