Как я могу написать сводную таблицу для отображения записей за каждый год в виде столбцов?

Я почти уверен, что мне нужен стержень, чтобы сделать это, но не могу понять это. (новичок SQL)

У меня есть такие данные:

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

Немного проблем с моими результатами...

  1. Есть две записи для CompanyID 0003. Я бы хотел, чтобы они были сгруппированы в одну запись.

  2. Я могу выбрать только одну компанию за раз, мне нужно выбрать несколько. Я старался

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

    Где @CompanyIDs - это строка типа '0003, 0010' - это не сбой, но результатом была просто пустая таблица с заголовками и без данных.

Любая помощь будет оценена... или дайте мне знать, если я неправильно понимаю поворот?

Большое спасибо!

Томас

РЕДАКТИРОВАТЬ: Использование Microsoft SQL Server Management Studio 2005 Express

ОБНОВЛЕНИЕ 2: Я выяснил, как объединить таблицы для получения более подробной информации, однако мне все еще нужно иметь возможность передавать идентификаторы компаний в виде строки с разделителями-запятыми. Любая помощь в этом будет оценена по достоинству.

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.

sql-server tsql pivot crosstab sql-server-2005
person tsdexter    schedule 19.12.2011    source источник
comment
Да, извините, я включил тег TSQL, но мог бы быть более конкретным. Майкрософт SQL Server 2005   -  person tsdexter    schedule 19.12.2011
comment
Я считаю более гибким просто выполнить GROUP BY на сервере, а затем связать результат в клиентском приложении.   -  person Patrick Honorez    schedule 19.12.2011

Ответы (1)


arrow_upward
2
arrow_downward

Попробуй это:

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

person Rubens Farias    schedule 19.12.2011
comment
Похоже, что это сработает, однако я добавил WHERE CompanyID = @CompanyID в самом конце, и это сработало, чтобы иметь одну строку со всеми годовыми данными, однако, когда я добавил WHERE CompanyID IN (@CompanyIDs), это не привело к ошибке. но он только что вернул пустую таблицу, вы знаете, как заставить работать предложение where? Большое спасибо за то, что завели меня так далеко. - person tsdexter; 19.12.2011
comment
спасибо за обновление, я попробую с xml - я очень ценю помощь .. Пожалуйста, посмотрите обновление, которое я пропустил в начальном вопросе ... Большое вам спасибо. - person tsdexter; 19.12.2011