How to use PIVOT to convert rows to columns in SQL SERVER DB?
- How-Tos FAQs
- December 16, 2018
To avoid coding your pivot altogether, you can use Datameer, which provides an easy-to-use end-user interface to do pivoting.
For another option on how to use PIVOT to convert rows to columns in SQL SERVER DB, let’s follow the example below;
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 123SELECT *
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 123Up Next:
Read How to INSERT values in a table using SELECT query in SQL?
