Convert rows into columns using PIVOT and columns into rows using UNPIVOT

You might have used pivot functionality in Microsoft Excel. Same you can do in MS SQL using  PIVOT and UNPIVOT statement. PIVOT is used to convert values in rows to use as column header and UNPIVOT does the reverse of PIVOT. See below example for better understanding.

--PIVOT examle

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

      DROP TABLE #sales

CREATE TABLE #sales (ProductName VARCHAR(200), SaleDate DATE, SalesQty INT)

INSERT INTO #sales(ProductName, SaleDate, SalesQty)
      VALUES('Bike', '2013-01-04', 56)
      ,('Bike', '2013-02-25', 45)
      ,('Bike', '2013-01-26', 87)
      ,('Bike', '2013-03-01', 12)
      ,('Bike', '2013-05-04', 62)
      ,('Bike', '2013-05-04', 73)
      ,('Bike', '2013-04-27', 34)
      ,('Scooter', '2013-03-12', 13)
      ,('Scooter', '2013-04-05', 75)
      ,('Scooter', '2013-01-09', 24)
      ,('Scooter', '2013-01-16', 76)
      ,('Scooter', '2013-01-16', 23)
      ,('Scooter', '2013-05-14', 36)
      ,('Scooter', '2013-01-16', 68)
      ,('Scooter', '2013-02-16', 88)

SELECT ProductName, Jan, Feb, Mar, Apr, May

FROM
      (SELECT ProductName, LEFT(DATENAME (month, SaleDate), 3) [monthName], ISNULL(SalesQty, 0) AS SalesQty  FROM #sales) AS A
PIVOT
      (
      SUM(SalesQty) --Aggregate function
      FOR [monthName] IN (Jan, Feb, Mar, Apr, May) --Column list
      ) AS B

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

      DROP TABLE #sales

 PIVOT has two important section first is aggregate function (see comment in above query) and second is FOR section which contains list of column names and original column which contains the column names.

See below for UNPIVOT example. I have used ouput of PIVOT query as input of UNPIVOT query.

--UNPIVOT example

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

      DROP TABLE #sales 

IF OBJECT_ID('tempdb..#sales_PIVOT') IS NOT NULL

      DROP TABLE #sales_PIVOT           

CREATE TABLE #sales (ProductName VARCHAR(200), SaleDate DATE, SalesQty INT)

INSERT INTO #sales(ProductName, SaleDate, SalesQty)

      VALUES('Bike', '2013-01-04', 56)

      ,('Bike', '2013-02-25', 45)

      ,('Bike', '2013-01-26', 87)

      ,('Bike', '2013-03-01', 12)

      ,('Bike', '2013-05-04', 62)

      ,('Bike', '2013-05-04', 73)

      ,('Bike', '2013-04-27', 34)

      ,('Scooter', '2013-03-12', 13)

      ,('Scooter', '2013-04-05', 75)

      ,('Scooter', '2013-01-09', 24)

      ,('Scooter', '2013-01-16', 76)

      ,('Scooter', '2013-01-16', 23)

      ,('Scooter', '2013-05-14', 36)

      ,('Scooter', '2013-01-16', 68)

      ,('Scooter', '2013-02-16', 88)

SELECT ProductName, Jan, Feb, Mar, Apr, May

      INTO #sales_PIVOT

FROM

      (SELECT ProductName, LEFT(DATENAME (month, SaleDate), 3) [monthName], ISNULL(SalesQty, 0) AS SalesQty  FROM #sales) AS A

PIVOT

      (

      SUM(SalesQty)

      FOR [monthName] IN (Jan, Feb, Mar, Apr, May)

      ) AS B

SELECT ProductName, [MonthName], SalesQty

FROM

      (SELECT ProductName, Jan, Feb, Mar, Apr, May FROM #sales_PIVOT) AS A

UNPIVOT

      (

      SalesQty FOR [monthName] IN (Jan, Feb, Mar, Apr, May)

      ) AS B

 

IF OBJECT_ID('tempdb..#sales') IS NOT NULL

      DROP TABLE #sales

IF OBJECT_ID('tempdb..#sales_PIVOT') IS NOT NULL

      DROP TABLE #sales_PIVOT

One important point to remember here is UNPIVOT is reverse process of PIVOT, but only limited for conversion of columns to rows. It does not regenerate the original result. This is due to aggregate function used by PIVOT. In our example we have used SUM function and there is no reverse function available for SUM to generate original value. There are some senarios where we can generate the original output using UNPIVOT. Please post if you have such example.