PIVOT in SELECT Statements - SQL Server to PostgreSQL Migration

In SQL Server you can use the PIVOT clause to rotate (pivot) column rows into columns. In PostgreSQL you can use CASE expressions.

Consider a sample table:

  CREATE TABLE orders (
     product VARCHAR(30),
     items INT  );  
 
  -- Sample rows
  INSERT INTO orders VALUES ('Apple', 1);
  INSERT INTO orders VALUES ('Orange', 3);
  INSERT INTO orders VALUES ('Apple', 7);
  INSERT INTO orders VALUES ('Orange', 11);

Consider the following query with PIVOT:

SQL Server:

  SELECT 'Total items' AS Total, [Apple], [Orange]
  FROM
  (
    SELECT * FROM orders
  ) t
  PIVOT
  (
    SUM(items) FOR product IN ([Apple], [Orange])
  ) p

Result:

In PostgreSQL you can use the CASE expressions as follows:

PostgreSQL:

  SELECT 'Total items' AS "Total", 
    SUM(CASE WHEN product = 'Apple' THEN items END) AS "Apple",
    SUM(CASE WHEN product = 'Orange' THEN items END) AS "Orange"
  FROM orders;

Result:

For more information, see SQL Server to PostgreSQL Migration.