Pivoting can be useful when the data needs to be modelled/flipped before it gets to the target.

SQL Server offers the PIVOT function to enable a relational set of records to be summarised and presented in the style of a Pivot Table. this operator was introduced in SQL Server 2005.

Example of the data in a table :

 OrderId    OrderDate   Amount
----------- ----------  ------
1           2007-01-01  10.50
2           2007-01-26  12.50
3           2007-01-30  12.00
4           2007-02-14  13.75
5           2007-02-20  10.00
6           2007-03-06  15.00
7           2007-03-10  17.50
8           2007-03-29  20.00

After pivotting the data, it would look like this:

 OrderYear   Jan   Feb   Mar
----------- ----- ----- -----
2007        35.00 23.75 52.50

Pivot operator for SQL Server 2005 and over:

1
2
3
4
5
6
7
8
9
10
SELECT OrderYear,
       [1] AS 'Jan',
       [2] AS 'Feb',
       [3] AS 'Mar'
FROM (SELECT DATEPART(yyyy, order_date),
             DATEPART(m, order_date),
             amount
      FROM Orders) AS O (OrderYear, month_nbr, amount)
PIVOT
(SUM(amount) FOR month_nbr IN ([1], [2], [3])) AS P;

Pivoting is still possible without using the PIVOT operator which was introduced in SQL Server 2005.

Using CASE

1
2
3
4
5
6
7
8
9
SELECT DATEPART(yyyy, order_date) AS OrderYear,
       SUM(CASE WHEN DATEPART(m, order_date) = 1
                THEN amount ELSE 0 END) AS 'Jan',
       SUM(CASE WHEN DATEPART(m, order_date) = 2
                THEN amount ELSE 0 END) AS 'Feb',
       SUM(CASE WHEN DATEPART(m, order_date) = 3
                THEN amount ELSE 0 END) AS 'Mar'
FROM Orders
GROUP BY DATEPART(yyyy, order_date)
Source
https://www.tangrainc.com/blog/2007/04/pivoting-data-in-sql-server/
https://www.ptr.co.uk/blog/how-do-you-create-pivot-tables-sql-server-queries
https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/

Categorized in:

SQL,

Last Update: May 18, 2024