Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
datameer banner faq

How to use PIVOT to convert rows to columns in SQL SERVER DB? 

  • How-Tos FAQs
  • December 16, 2018

Let us set up the table below for using PIVOT. 

CREATE TABLE orders (
    [order_id] INT,
    [year] INT,
    [total_order] INT
);

INSERT INTO orders(order_id, [year], total_order)
VALUES
(1, 2001, 96),
(2, 2001, 138),
(3, 2002, 37),
(4, 2002, 59),
(5, 2002, 282),
(6, 2003, 212),
(7, 2003, 78),
(8, 2004, 97),
(9, 2004, 60),
(10, 2005, 123);

There are two parts to a PIVOT statement. One is the PIVOT part, and the other is the SELECT part. In the PIVOT part, we specify the values of the row, which needs to be pivoted like [2001], [2002] … in the below query. Then we specify the action on the value like SUM, MAX, COUNT, AVG, etc. In the SELECT part, we specify all the columns that need to be shown in the pivoted output.

 PIVOT using SUM operation:

SELECT *
FROM
(
  SELECT  [year], total_order
  FROM orders
) src
PIVOT
(
  SUM(total_order)
  FOR [year] IN ([2001], [2002], [2003], [2004], [2005])
) piv;

#Output
2001       2002       2003       2004       2005
------------------------------------------------------------------
234         378         290         157         123

A Faster Way To Build Data Models

Mix SQL and no code to create data models in a few clicks vs months.

Try Datameer Today

PIVOT using MAX operation:

SELECT *
FROM
(
  SELECT  [year], total_order
  FROM orders
) src
PIVOT
(
  MAX(total_order)
  FOR [year] IN ([2001], [2002], [2003], [2004], [2005])
) piv;

#Output

2001       2002       2003       2004       2005
-----------------------------------------------------------------
138         282         212         97           123

Up Next:

Read How to INSERT values in a table using SELECT query in SQL?

More Resources We Think You Might Like

SQL FAQ Feat

How to concatenate data from different rows of ...

Let us consider the following table: CREATE TABLE fruits (     ID INT,     NAME VARCHAR(50) ) INS...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to select the first row of each GROUP BY in...

Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to INSERT values in a table using SELECT qu...

This can be done in below: INSERT INTO orders (ID, customer_name, order_date, total_orders) SELEC...

  • How-Tos FAQs
  • December 16, 2018