A look at various options for creating crosstabs or pivot tables using PostgreSQL.

CASE-Conditional-Expression-CTE.sql

  
--- CASE Conditional Expression crosstab using a CTE
WITH cte AS (
    SELECT  salesman,
        SUM(CASE order_month WHEN 1 THEN order_total ELSE 0 END) AS Jan,
        SUM(CASE order_month WHEN 2 THEN order_total ELSE 0 END) AS Feb,
        SUM(CASE order_month WHEN 3 THEN order_total ELSE 0 END) AS Mar,
        SUM(CASE order_month WHEN 4 THEN order_total ELSE 0 END) AS Apr,
        SUM(order_total) AS total
    FROM
        (
          SELECT e.last_name  || ' ' || e.first_name AS salesman,
            date_part('month', order_date) AS order_month,
            ROUND((quantity * unit_price)::numeric, 2) AS order_total
          FROM employees e
          JOIN orders o ON e.employee_id = o.employee_id
          JOIN order_details od ON o.order_id = od.order_id
          WHERE date_part('year', order_date) = 1997
              AND  date_part('month', order_date) BETWEEN 1 AND 4
        ) employee_sales
    GROUP by 1
)
SELECT * FROM cte
UNION ALL
SELECT  'Totals ',
    SUM(jan),
    SUM(feb),
    SUM(mar),
    SUM(apr),
    SUM(total) AS total
FROM cte
  

CASE-Conditional-Expression-Union-All.sql

  --- UNION ALL operator to include totals for each month
  UNION ALL
  SELECT  'Totals ',
      SUM(CASE order_month WHEN 1 THEN order_total ELSE 0 END) AS Jan,
      SUM(CASE order_month WHEN 2 THEN order_total ELSE 0 END) AS Feb,
      SUM(CASE order_month WHEN 3 THEN order_total ELSE 0 END) AS Mar,
      SUM(CASE order_month WHEN 4 THEN order_total ELSE 0 END) AS Apr,
      SUM(order_total) AS total
  FROM
      (
        SELECT
            date_part('month', order_date) AS order_month,
        ROUND(SUM(quantity * unit_price)::numeric, 2) AS order_total
    FROM
        orders o
    JOIN order_details od ON o.order_id = od.order_id
    WHERE date_part('year', order_date) = 1997
        AND  date_part('month', order_date) BETWEEN 1 AND 4
    GROUP BY 1
  ) employee_sales

CASE-Conditional-Expression.sql

---- Crosstabs Using CASE Conditional Expression
SELECT  salesman,
      SUM(CASE order_month WHEN 1 THEN order_total ELSE 0 END) AS Jan,
      SUM(CASE order_month WHEN 2 THEN order_total ELSE 0 END) AS Feb,
      SUM(CASE order_month WHEN 3 THEN order_total ELSE 0 END) AS Mar,
      SUM(CASE order_month WHEN 4 THEN order_total ELSE 0 END) AS Apr,
      SUM(order_total) AS total
  FROM
      (
        SELECT
          e.last_name  || ' ' || e.first_name AS salesman,
      date_part('month', order_date) AS order_month,
      ROUND((quantity * unit_price)::numeric, 2) AS order_total
    FROM employees e
    JOIN orders o ON e.employee_id = o.employee_id
    JOIN order_details od ON o.order_id = od.order_id
    WHERE date_part('year', order_date) = 1997
        AND  date_part('month', order_date) BETWEEN 1 AND 4
      ) employee_sales
  GROUP by 1

check-tablefunc-extension-installed.sql

--- Check whether tablefunc extension is installed
SELECT COUNT(*) FROM pg_extension WHERE extname='tablefunc';

Crosstabs-FILTER-clause.sql

---- Crosstabs Using Aggregate FILTER Clause
WITH cte As (
		SELECT salesman ,
		    COALESCE(SUM(order_total) FILTER ( WHERE order_month = 1), 0) AS Jan,
		    COALESCE(SUM(order_total) FILTER ( WHERE order_month = 2), 0) AS Feb,
		    COALESCE(SUM(order_total) FILTER ( WHERE order_month = 3), 0) AS Mar,
		    COALESCE(SUM(order_total) FILTER ( WHERE order_month = 4), 0) AS Apr,
		    COALESCE(SUM(order_total), 0) AS total
		FROM
		    (
		        SELECT  e.last_name  || ' ' || e.first_name AS salesman ,
    	            date_part('month', order_date) AS order_month,
    	            ROUND((quantity * unit_price)::numeric, 2) AS order_total
    	        FROM
    	            employees e
    	            JOIN orders o ON e.employee_id = o.employee_id
    	            JOIN order_details od ON o.order_id = od.order_id
    	        WHERE date_part('year', order_date) = 1997
	            AND  date_part('month', order_date) BETWEEN 1 AND 4
		    ) employee_sales
		GROUP by 1
)
SELECT * FROM cte  
UNION ALL
SELECT  'Totals ',
    SUM(jan),
    SUM(feb),
    SUM(mar),
    SUM(apr),
    SUM(total) AS total
FROM cte

postgreSQL-crosstab-function.sql

-- Using PostgreSQL crosstab() Function
 SELECT *
    FROM crosstab(
        $$
          SELECT e.last_name  || ' ' || e.first_name salesman,
              date_part('Mon',order_date) AS order_month,
              SUM(ROUND((quantity * unit_price)::numeric, 2)) AS order_total
          FROM employees e
          JOIN orders o ON e.employee_id = o.employee_id
          JOIN order_details od ON o.order_id = od.order_id
          WHERE date_part('year', order_date) = 1997
              AND  date_part('month', order_date) BETWEEN 1 AND 4
          GROUP BY 1, 2
          UNION ALL
          SELECT 'month_total', date_part('month', order_date) AS order_month,
            ROUND(SUM(quantity * unit_price)::numeric, 2) AS order_total
          FROM orders o
          JOIN order_details od ON o.order_id = od.order_id
          WHERE date_part('year', order_date) = 1997
              AND  date_part('month', order_date) BETWEEN 1 AND 4
          GROUP BY 1,2
        $$,
        $$
          SELECT DISTINCT date_part('month', order_date) AS order_month
          FROM orders
          WHERE date_part('year', order_date) = 1997
              AND  date_part('month', order_date) BETWEEN 1 AND 4
          ORDER BY 1
        $$
        ) AS ct (salesman text, Jan numeric, Feb numeric, Mar numeric, Apr numeric);

    

postgreSQL-crosstab-more-than-3-columns.sql

       
-- Using A PostgreSQL crosstab() Function With More Than Three Columns
SELECT employee AS "Salesman", title AS "Title", emp_total AS "Total", COALESCE(Jan, 0) AS "Jan", 
    COALESCE(Feb, 0) AS "Feb", mar AS  "Mar", COALESCE(Apr, 0) AS "Apr"
FROM crosstab(
    $$
        SELECT e.last_name  || ' ' || e.first_name as salesman, 
		    e.title ,
		    et.emp_total ,
		    date_part('Mon',order_date) AS order_month,
		   SUM(ROUND((quantity * unit_price)::numeric, 2)) AS order_total 
		FROM employees e
	    JOIN (
			    SELECT e.employee_id ,
				    SUM(ROUND((quantity * unit_price)::numeric, 2)) AS emp_total
				FROM  employees e
				    JOIN orders o ON e.employee_id = o.employee_id
				    JOIN order_details od ON o.order_id = od.order_id
				WHERE date_part('year', order_date) = 1997
				    AND  date_part('month', order_date) BETWEEN 1 AND 4
				GROUP BY 1
	    	) et on e.employee_id = et.employee_id
	    JOIN orders o ON e.employee_id = o.employee_id
	    JOIN order_details od ON o.order_id = od.order_id
		WHERE date_part('year', order_date) = 1997
		    AND  date_part('month', order_date) BETWEEN 1 AND 4
		GROUP BY 1,2,3,4
		UNION ALL
		SELECT '' AS salesman,
		    'Month Total' AS title,
			(
    			SELECT SUM(ROUND((quantity * unit_price)::numeric, 2)) AS order_total 
        		FROM orders o
        		JOIN order_details od ON o.order_id = od.order_id
        		WHERE date_part('year', order_date) = 1997
        		    AND  date_part('month', order_date) BETWEEN 1 AND 4
			) AS col_total,
		    date_part('Mon',order_date) AS order_month,
		    SUM(ROUND((quantity * unit_price)::numeric, 2)) AS order_total
		FROM employees e
	    JOIN orders o ON e.employee_id = o.employee_id
	    JOIN order_details od ON o.order_id = od.order_id
		WHERE date_part('year', order_date) = 1997
		    AND  date_part('month', order_date) BETWEEN 1 AND 4
		GROUP BY 1,2,3, 4
    $$,
    $$ 
      SELECT DISTINCT date_part('month', order_date) AS order_month
      FROM orders
      WHERE date_part('year', order_date) = 1997
          AND  date_part('month', order_date) BETWEEN 1 AND 4
      ORDER BY 1
    
    $$
    ) AS ct (employee text, title text, emp_total numeric, Jan numeric, Feb numeric, Mar numeric, Apr numeric);
    

postgreSQL-crosstab-rearrange-extra-columns.sql

-- Using ARRAY Data Type To Re-Arrange Extra Crosstab Columns
    SELECT employee[1] AS "Salesman", employee[2] AS "Title", 
		COALESCE(Jan, 0) AS "Jan", COALESCE(Feb, 0) AS "Feb", mar AS  "Mar", COALESCE(Apr, 0) AS "Apr"
		, employee[3]::numeric AS "Total"
    FROM crosstab(
        $$
            SELECT ARRAY[e.last_name  || ' ' || e.first_name, e.title, et.emp_total::text] AS employee,
			   date_part('Mon',order_date) AS order_month,
			   SUM(ROUND((quantity * unit_price)::numeric, 2)) AS order_total 
			FROM employees e
		    JOIN (
				    SELECT e.employee_id ,
					    SUM(ROUND((quantity * unit_price)::numeric, 2)) AS emp_total
					FROM  employees e
					    JOIN orders o ON e.employee_id = o.employee_id
					    JOIN order_details od ON o.order_id = od.order_id
					WHERE date_part('year', order_date) = 1997
					    AND  date_part('month', order_date) BETWEEN 1 AND 4
					GROUP BY 1
		    	) et on e.employee_id = et.employee_id
		    JOIN orders o ON e.employee_id = o.employee_id
		    JOIN order_details od ON o.order_id = od.order_id
			WHERE date_part('year', order_date) = 1997
			    AND  date_part('month', order_date) BETWEEN 1 AND 4
			GROUP BY 1,2
			UNION ALL
			SELECT ARRAY[
			        '', 'Month Total',
					(
						SELECT SUM(ROUND((quantity * unit_price)::numeric, 2))::text
						FROM orders o
						JOIN order_details od ON o.order_id = od.order_id
						WHERE date_part('year', order_date) = 1997
						    AND  date_part('month', order_date) BETWEEN 1 AND 4
					)
				 ] AS employee,
			    date_part('Mon',order_date) AS order_month,
			    SUM(ROUND((quantity * unit_price)::numeric, 2)) AS order_total
			FROM employees e
			    JOIN orders o ON e.employee_id = o.employee_id
			    JOIN order_details od ON o.order_id = od.order_id
			WHERE date_part('year', order_date) = 1997
			    AND  date_part('month', order_date) BETWEEN 1 AND 4
			GROUP BY 1,2
        $$,
        $$ 
          SELECT DISTINCT date_part('month', order_date) AS order_month
          FROM orders
          WHERE date_part('year', order_date) = 1997
              AND  date_part('month', order_date) BETWEEN 1 AND 4
          ORDER BY 1  
        $$
        ) AS ct (employee text[], Jan numeric, Feb numeric, Mar numeric, Apr numeric);