SQL Server Pivot Function – Converts Rows To Columns – 1 Cool Example!

Sometimes you got business requirements to create dashboard and you must use SQL Server Pivot function to convert rows to columns. You can transpose them on two ways: using CASE or PIVOT function. In this post I will show you how to convert rows to columns at SQL Server on these two ways.

In other words converting rows to column can be called: transposition. The Cambridge dictionary describes the transpositions as:

to change something from one position to another, or to exchange the positions of two things:

https://dictionary.cambridge.org/dictionary/english/transpose

PIVOT

In the context of databases and data analysis, a pivot is a type of operation that rearranges, or pivots, the data in a table by turning the unique values in one column into separate columns and aggregating the data in other columns accordingly.

For example, suppose you have a table that contains sales data for a store, with columns for the product, the customer, and the amount of the sale. If you want to see the total sales for each product, you can pivot the table by turning the product column into rows and summing up the sales amounts for each product.

Pivot operations can be useful for organizing and summarizing data, and they can be performed using various spreadsheet and database software.

SQL Server Pivot Function

To explain you how to use SQL Server Pivot function, first we create PRODUCT table and insert some samples records in it.

CREATE TABLE MASTER.DBO.PRODUCT (
  PRODUCER_NME VARCHAR(100),
  PRODUCT_NME  VARCHAR(100),
  R_YR  INT,
  R_MTH  INT,
  SOLD_NO NUMERIC(7,2)
);

And this is the data preview:

SQL PIVOT

SQL Server PIVOT function allows us quickly transpose rows into columns. Let’s look at the example below! (SQL Server How to use Pivot in SQL function)

SELECT *
FROM 
(
  SELECT PRODUCER_NME, PRODUCT_NME, R_YR, R_MTH,SOLD_NO
  FROM MASTER.DBO.PRODUCT
) T
PIVOT
(
  SUM(SOLD_NO)
  FOR R_MTH IN ([1], [2], [3])
) PIV;

R_MTH in my table is the attribute which I want to see as columns, but as value for this attributes I want to see sum of the SOLD_NO values. I can write SELECT *, because my table not contain any additional attributes – in case when your table have a lot of columns, probably you will must limit them.

Convert Rows To Columns

The screenshot below presents like my data look after using the PIVOT function in Microsoft SQL Server:

CASE statement

You can get the same results as using the pivot function using the CASE statement. CASE statement is used more often than PIVOT so you can feel more comfortable using it. But remember what is the better, quicker and shorter depends how many attributes, calculation and conditions you need to use. And in most cases the PIVOT function wins 🙂

SELECT 
	PRODUCER_NME, 
	PRODUCT_NME, 
	R_YR,
	SUM(CASE WHEN R_MTH = 1 THEN SOLD_NO ELSE NULL END) AS MTH_1,
	SUM(CASE WHEN R_MTH = 2 THEN SOLD_NO ELSE NULL END) AS MTH_2,
	SUM(CASE WHEN R_MTH = 3 THEN SOLD_NO ELSE NULL END) AS MTH_3
FROM MASTER.DBO.PRODUCT 
GROUP BY 
	PRODUCER_NME, 
	PRODUCT_NME, 
	R_YR;

The SELECT statement above can give you the same result like the PIVOT function.

Example With Sales Data

The PIVOT function in SQL Server is used to rotate data from a table into a more readable format. It converts rows into columns and aggregates data within the new columns.

Here’s an example of how to use the PIVOT function:

Suppose we have a table called “sales” that contains data on the number of products sold in different regions. The table looks like this:

We can use the PIVOT function to rotate the data so that each product is a separate column and the quantities are aggregated by region:

SELECT *
FROM
(
  SELECT Region, Product, Quantity
  FROM sales
) AS s
PIVOT
(
  SUM(Quantity)
  FOR Product IN ([A], [B])
) AS p;

The resulting table would look like this:

Note that the PIVOT function requires an aggregation function (e.g. SUM, AVG, MAX, etc.) to be specified in order to combine multiple rows into a single column. In this example, we used SUM to add up the quantities for each product and region.

Scripts

If you want to download all the scripts using in this post, you can find on our GitLab repository!

I hope this post was helpful for you. If you like this post please share it with your friends!

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!