SQL server join tables and pivot

Question

I have two tables with data

TABLE 1

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

TABLE 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      |

I need a query which will produce

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

I am pretty new to pivoting and not sure if pivot is way to go for this or not.

Answer 1

This should work:

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
;

Or alternately:

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
;

These have the same resultset and may in fact be treated the same by the query optimizer, but possibly not. The big difference comes into play when you start putting conditions on the Sale table--you should test and see which query works better.

May I suggest, however, that you do the pivoting in the presentation layer? If, for example, you are using SSRS it is quite easy to use a matrix control that will do all the pivoting for you. That is best, because then if you add a new Category, you won't have modify all your SQL code!

There is a way to dynamically find the column names to pivot, but it involves dynamic SQL. I don't really recommend that as the best way, either, though it is possible.

Another way that could work would be to preprocess this query--meaning to set a trigger on the Category table that rewrites a VIEW to contain all the extant categories that exist. This does solve a lot of the other problems I've mentioned, but again, using the presentation layer is best.

Note: If your column names (that were formerly values) are numbers or begin with a number, you must quote them with square brackets as in PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P. Alternately, you can modify the values before they get to the PIVOT part of the query to prepend some letters, so that the column list doesn't need escaping. For further reading on this check out the rules for identifiers in SQL Server.