PIVOT and UNPIVOT

There may be situations while writing SQL code where we would like to have totals or aggregates of columns alongside a column, e.g number of points scored by a sales person with their sales, as well as separate sales by month or year.

So lets first create a table which represents sales data as below

CREATE TABLE #tempTest
(
  empName VARCHAR(25),
  noOfSales INT,
  [monthName] VARCHAR(3),
  Points INT
)

Just add a few rows of data to this table

INSERT INTO #tempTest VALUES('James',2,'Jan', 2);
INSERT INTO #tempTest VALUES('Jason',1,'Jan', 1);
INSERT INTO #tempTest VALUES('Mark',3,'Jan', 3);
INSERT INTO #tempTest VALUES('Mark',1,'Jan', 1);
INSERT INTO #tempTest VALUES('James',1,'Feb', 1);
INSERT INTO #tempTest VALUES('James',2,'Feb', 2);
INSERT INTO #tempTest VALUES('Jason',3,'Feb', 3);
INSERT INTO #tempTest VALUES('Jason',2,'Feb', 2);
INSERT INTO #tempTest VALUES('Mark',1,'Mar', 1);
INSERT INTO #tempTest VALUES('James',2,'Mar', 2);
INSERT INTO #tempTest VALUES('Jason',2,'Mar', 2);

Now to show how many sales each sales person gets per month we could use PIVOT to achieve this easily

SELECT
  empName,
  [Jan] AS Total_Sales_Jan,
  [Feb] AS Total_Sales_Feb,
  [Mar] AS Total_Sales_Mar
FROM
  (
    SELECT
      empName, noOfSales, [monthName]
    FROM
      #tempTest
  ) AS Source PIVOT (SUM(noOfSales) for [monthName] IN ([Jan],[Feb],[Mar]) )AS PivotTable

This query results in the following result

Results Pivot 1

Please note that PIVOT can have only one non pivoted column, Example below shows how PIVOT can be used incorrectly

SELECT
  empName,
  SUM([Jan]) AS Jan,
  SUM([Feb]) AS Feb,
  SUM([Mar]) AS Mar,
  SUM(Points) AS Points
FROM
  (
    SELECT
      empName,
      noOfSales,
      [monthName],
      Points
    FROM
      #tempTest
  ) AS Source PIVOT (SUM(noOfSales) for [monthName] IN ([Jan],[Feb],[Mar]) )AS PivotTable
GROUP BY
  empName

Results Pivot 2

Note that the results are incorrect for Points, they dont sum up correctly for each sales person as expected, this is because you cannot use more than one unpivoted column

UNPIVOT is the exact opposite of PIVOT although the usage of it is likely to be minimal there are chances this may be used in some occassions