Pivot/Unpivot

Code

-- Use CASE WHEN for pivot
SELECT
    dept,
    SUM(CASE WHEN job_title = 'Engineer' THEN 1 ELSE 0 END) AS engineers,
    SUM(CASE WHEN job_title = 'Manager' THEN 1 ELSE 0 END) AS managers,
    SUM(CASE WHEN job_title = 'Designer' THEN 1 ELSE 0 END) AS designers,
    COUNT(*) AS total
FROM employees
GROUP BY dept;

-- Use crosstab (requires tablefunc extension)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT dept, job_title, COUNT(*)::int
     FROM employees
     GROUP BY dept, job_title
     ORDER BY dept, job_title'
) AS ct(dept TEXT, engineers INT, managers INT, designers INT);

-- UNPIVOT (UNION ALL)
SELECT dept, 'Engineer' AS role, engineer_count AS count FROM dept_summary
UNION ALL
SELECT dept, 'Manager' AS role, manager_count FROM dept_summary;

Explanation

Use CASE WHEN or crosstab for pivot; UNION ALL for unpivot.

More SQL Snippets