SQL-сервер соединяет таблицы и сводные

У меня есть две таблицы с данными

ТАБЛИЦА 1

    ---------------------------------------------------
    | SALEID | SOLDBY | SALEPRICE | MARGIN |   DATE   |
    |  1     |  'aa'  |  10,000   |   10   | 2013-1-1 |
    |  2     |  'bb'  |  25,000   |    5   | 2013-5-1 |

ТАБЛИЦА 2

    ---------------------------------------------------
    | SALEITEMID | SALEID | SALEPRICE | CATEGORY |
    |  1         |  1     |   6,000   | BOOKS    |
    |  2         |  1     |   4,000   | PRINTING |
    |  3         |  2     |   5,000   | BOOKS    |
    |  4         |  2     |   12,000  | PRINTING |
    |  5         |  2     |   8,000   | DVD      |

Мне нужен запрос, который будет создавать

TAB3

    --------------------------------------------------------------------------------
    | SALEID | SOLDBY | SALEPRICE | MARGIN |   DATE   |  BOOKS  | PRINTING | DVD
    |  1     |  'aa'  |  10,000   |   10   | 2013-1-1 |  6,000  |  4,000   | 0
    |  2     |  'bb'  |  25,000   |    5   | 2013-5-1 |  5,000  | 12,000   | 8,000

Я новичок в повороте и не уверен, что поворот - это путь для этого или нет.

Ответы

Ответ 1

Это должно работать:

WITH Sales AS (
   SELECT
      S.SaleID,
      S.SoldBy,
      S.SalePrice,
      S.Margin,
      S.Date,
      I.SalePrice,
      I.Category
   FROM
      dbo.Sale S
      INNER JOIN dbo.SaleItem I
         ON S.SaleID = I.SaleID
)
SELECT *
FROM
   Sales
   PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
;

Или поочередно:

SELECT
   S.SaleID,
   S.SoldBy,
   S.SalePrice,
   S.Margin,
   S.Date,
   I.Books,
   I.Printing,
   I.DVD
FROM
   dbo.Sale S
   INNER JOIN (
      SELECT *
      FROM
         (SELECT SaleID, SalePrice, Category FROM dbo.SaleItem) I
         PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
   ) I ON S.SaleID = I.SaleID
;

Они имеют одинаковый набор результатов и на самом деле могут быть обработаны одним и тем же оптимизатором запросов, но, возможно, нет. Большая разница вступает в игру при запуске условий в таблице Sale - вы должны проверить и посмотреть, какой запрос работает лучше.

Могу ли я предположить, однако, что вы делаете поворот в слое презентации? Если, например, вы используете SSRS, довольно просто использовать матричный элемент управления, который будет делать все поворот для вас. Это лучше всего, потому что, если вы добавите новый Category, вы не будете модифицировать весь свой код SQL!

Существует способ динамического поиска имен столбцов для поворота, но он включает динамический SQL. Я даже не рекомендую это как лучший способ, хотя это возможно.

Другой способ, который мог бы работать, состоял бы в препроцессе этого запроса - это означает установить триггер в таблице Category, которая перезаписывает VIEW, чтобы содержать все существующие существующие категории. Это решает многие другие проблемы, о которых я упомянул, но опять же, лучше использовать слой презентации.

Примечание. Если имена столбцов (ранее были значениями) являются номерами или начинаются с числа, вы должны процитировать их с квадратными скобками, как в PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P. Кроме того, вы можете изменить значения до того, как они дойдут до части PIVOT запроса, чтобы добавить несколько букв, чтобы список столбцов не нуждался в экранировании. Для дальнейшего ознакомления с этим ознакомьтесь с правилами для идентификаторов в SQL Server.