T-SQL – Transpose rows into columns or columns into rows

It is quite common to need to transpose rows into columns or columns into rows for reporting purposes. The simplest way to do this change is using the PIVOT operator.

Using “Static” Pivot columns is one way to change the row data into columns, but the column names must be known up front.


SELECT *
FROM
(SELECT CustomerId, YEAR(invoicedatepaid) AS InvoiceYear,
RetailAmount
FROM Invoice) as InvoiceValue
PIVOT(SUM(RetailAmount) FOR InvoiceYear IN ([2013], [2014])) AS SUBTAB;

The result of this query will the customer sales per year

CustomerId

2013

2014

Cust1

10023.12

18923.12

Cust2

11135.42

14312.36

Using “Static” UnPivot allows you to change the column data into rows, but the column names must be known up front. If the previous sample results was a table called CustomerYearlySales, the command to convert to rows would be:


SELECT CustomerId, InvoiceYear, RetailAmount
FROM CustomerYearlySales
UNPIVOT(RetailAmount FOR InvoiceYear IN ([2013], [2014])) AS SUBTAB;

This command will split the results up into rows for each customer and year.
The result of this query will be:

CustomerId

InvoiceYear

RetailAmount

Cust1

2013

10023.12

Cust1

2014

18923.12

Cust2

2013

11135.42

Cust2

2014

14312.36