Concepts/Advanced/

PIVOT

An introduction to PIVOT.

PIVOT enables you to see rows as columns in a query result.

For example, if we started with the query:

SELECT pipeline.product AS product,
       teams.regional_office AS office,
       SUM(pipeline.close_value) AS value
  FROM sales_pipeline AS pipeline, sales_teams AS teams
 WHERE sales_pipeline.sales_agent = sales_teams.sales_agent
   AND sales_pipeline.deal_stage = "Won"
 GROUP BY pipeline.product, office
 ORDER BY pipeline.product, office
siyeh/sql-crm-example-data Run query Copy code

We would get the results:

product office value
GTK 500 West 400,612
GTX Basic Central 182,839
GTX Basic East 117,331
GTX Basic West 199,093
GTX Plus Basic Central 276,512
GTX Plus Basic East 200,679
GTX Plus Basic West 228,084
GTX Plus Pro Central 931,904
GTX Plus Pro East 822,168
GTX Plus Pro West 875,579
GTXPro Central 1,104,109
GTXPro East 1,285,647
GTXPro West 1,120,822
MG Advanced Central 828,947
MG Advanced East 655,914
MG Advanced West 731,526
MG Special Central 21,982
MG Special East 8,855
MG Special West 12,931

If instead we wanted to see a column for each of the offices with the sales for each product listed in it, we could modify our initial query like this:

WITH table1 AS (
    SELECT pipeline.product AS product,
           teams.regional_office AS office,
           pipeline.close_value AS value
      FROM sales_pipeline AS pipeline, sales_teams AS teams
     WHERE sales_pipeline.sales_agent = sales_teams.sales_agent
           AND sales_pipeline.deal_stage = "Won"
)
SELECT *
  FROM table1
 PIVOT (SUM(value) FOR office IN ("East", "Central", "West"))
 ORDER BY product
siyeh/sql-crm-example-data Run query Copy code
To make your query easier to read, it's a good practice to use a `WITH` clause to simplify the data you're going to pivot.

And the results would look like this:

product East Central West
GTK 500 400,612
GTX Basic 117331 182,839 199,093
GTX Plus Basic 200,679 276,512 228,084
GTX Plus Pro 822,168 931,904 875,579
GTXPro 1285,647 1,104,109 1,120,822
MG Advanced 655,914 828,947 731,526
MG Special 8,855 21,982 12,931

A PIVOT clause is composed of three parts: the value that’s being aggregated, IN followed by the column that’s being replaced by two or more columns, and FOR followed by specified values from the column being replaced that will become new columns. All the values to become new columns must be specified. If a value exists that isn’t specified, it will not be included in the PIVOT query results.

PIVOT can’t be used with WHERE, GROUP BY, or HAVING after the PIVOT clause.