Guide des tableaux croisés dynamiques en SQL

SELECT
  row_column,
  SUM(CASE WHEN pivot_column = 'value 1' THEN amount ELSE 0 END) AS value_1,
  SUM(CASE WHEN pivot_column = 'value 2' THEN amount ELSE 0 END) AS value_2
FROM table_name
GROUP BY row_column;

-- Input: sales
-- product  | quarter | units
-- ---------+---------+------
-- Keyboard | Q1      | 120
-- Keyboard | Q2      | 95
-- Monitor  | Q1      | 40
-- Monitor  | Q2      | 55
-- Mouse    | Q2      | 210

SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN units ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN units ELSE 0 END) AS q2
FROM sales
GROUP BY product
ORDER BY product;

-- Output:
-- product  | q1  | q2
-- ---------+-----+----
-- Keyboard | 120 | 95
-- Monitor  | 40  | 55
-- Mouse    | 0   | 210

-- Input: the sales table above

SELECT
  product,
  ROUND(AVG(CASE WHEN quarter = 'Q1' THEN units ELSE 0 END), 1) AS q1_else_0,
  ROUND(AVG(CASE WHEN quarter = 'Q1' THEN units END), 1) AS q1_no_else
FROM sales
GROUP BY product
ORDER BY product;

-- Output:
-- product  | q1_else_0 | q1_no_else
-- ---------+-----------+-----------
-- Keyboard | 60.0      | 120.0
-- Monitor  | 20.0      | 40.0
-- Mouse    | 0.0       | NULL

-- Input: the sales table above

-- PostgreSQL and SQLite
SELECT
  product,
  SUM(units) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(units) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY product
ORDER BY product;

-- SQL Server
SELECT product, [Q1] AS q1, [Q2] AS q2
FROM (SELECT product, quarter, units FROM sales) AS src
PIVOT (SUM(units) FOR quarter IN ([Q1], [Q2])) AS p
ORDER BY product;

-- Output:
-- product  | q1   | q2
-- ---------+------+----
-- Keyboard | 120  | 95
-- Monitor  | 40   | 55
-- Mouse    | NULL | 210

-- Input: reviews
-- app      | platform | rating
-- ---------+----------+-------
-- Budgetly | iOS      | 5
-- Budgetly | iOS      | 4
-- Budgetly | Android  | 3
-- FitTrack | Android  | 4
-- FitTrack | Android  | 5

-- In SQL Server, AVG of an integer column drops the decimals: use rating * 1.0
SELECT
  app,
  ROUND(AVG(CASE WHEN platform = 'iOS' THEN rating END), 1) AS ios,
  ROUND(AVG(CASE WHEN platform = 'Android' THEN rating END), 1) AS android
FROM reviews
GROUP BY app
ORDER BY app;

-- Output:
-- app      | ios  | android
-- ---------+------+--------
-- Budgetly | 4.5  | 3.0
-- FitTrack | NULL | 4.5

-- Input: orders
-- id | region | channel | amount
-- ---+--------+---------+-------
-- 1  | North  | web     | 120.00
-- 2  | North  | store   | 80.00
-- 3  | North  | web     | 45.50
-- 4  | South  | store   | 210.00
-- 5  | South  | store   | 64.00

SELECT
  region,
  COUNT(CASE WHEN channel = 'web' THEN 1 END) AS web_orders,
  SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END) AS web_sales,
  COUNT(CASE WHEN channel = 'store' THEN 1 END) AS store_orders,
  SUM(CASE WHEN channel = 'store' THEN amount ELSE 0 END) AS store_sales
FROM orders
GROUP BY region
ORDER BY region;

-- Output:
-- region | web_orders | web_sales | store_orders | store_sales
-- -------+------------+-----------+--------------+------------
-- North  | 2          | 165.50    | 1            | 80.00
-- South  | 0          | 0         | 2            | 274.00

-- Input: tickets
-- id | agent | priority
-- ---+-------+---------
-- 1  | Priya | urgent
-- 2  | Priya | normal
-- 3  | Marco | normal
-- 4  | Priya | normal
-- 5  | Marco | low

SELECT
  agent,
  COUNT(CASE WHEN priority = 'low' THEN 1 END) AS low,
  COUNT(CASE WHEN priority = 'normal' THEN 1 END) AS normal,
  COUNT(CASE WHEN priority = 'urgent' THEN 1 END) AS urgent,
  COUNT(*) AS total
FROM tickets
GROUP BY agent
ORDER BY agent;

-- Output:
-- agent | low | normal | urgent | total
-- ------+-----+--------+--------+------
-- Marco | 1   | 1      | 0      | 2
-- Priya | 0   | 2      | 1      | 3

-- Input: user_settings
-- user_id | setting  | value
-- --------+----------+--------------
-- 1       | theme    | dark
-- 1       | language | en
-- 2       | language | de
-- 2       | timezone | Europe/Berlin
-- 3       | theme    | light

SELECT
  user_id,
  MAX(CASE WHEN setting = 'theme' THEN value END) AS theme,
  MAX(CASE WHEN setting = 'language' THEN value END) AS language,
  MAX(CASE WHEN setting = 'timezone' THEN value END) AS timezone
FROM user_settings
GROUP BY user_id
ORDER BY user_id;

-- Output:
-- user_id | theme | language | timezone
-- --------+-------+----------+--------------
-- 1       | dark  | en       | NULL
-- 2       | NULL  | de       | Europe/Berlin
-- 3       | light | NULL     | NULL