SQL: Transposing rows without subqueries

transpose_rows.sql

-- Say we have this table called selected_colors:
-- +----------------+
-- |  type  | value |
-- +--------+-------+
-- | blue   | 1     |
-- | red    | 4     |
-- | yellow | 8     |
-- | red    | 12    |
-- | blue   | 5     |
-- | blue   | 0     |
-- +--------+-------+

-- And we want this object:
-- [blue] = 6, [red] = 16, [yellow] = 8

SELECT
    SUM(CASE sc.type WHEN 'blue' THEN sc.value ELSE 0 END) AS blue,
    SUM(CASE sc.type WHEN 'red' THEN sc.value ELSE 0 END) AS red,
    SUM(CASE sc.type WHEN 'yellow' THEN sc.value ELSE 0 END) AS yellow
FROM selected_colors AS sc;

-- Result:
-- ======
-- +------+-----+--------+
-- | blue | red | yellow |
-- +------+-----+--------+
-- | 6    | 16  | 8      |
-- +------+-----+--------+

-- Why? How?
-- Is really simple in fact, lets add a GROUP BY value (works in this
-- certain case because every value is distinct)

-- Original table:     Result:
-- ==============      ======
-- +----------------+  +------+-----+--------+
-- |  type  | value |  | blue | red | yellow |
-- +--------+-------+  +------+-----+--------+
-- | blue   | 1     |  | 1    | 0   | 0      |
-- | red    | 4     |  | 0    | 4   | 0      |
-- | yellow | 8     |  | 0    | 0   | 8      |
-- | red    | 12    |  | 0    | 12  | 0      |
-- | blue   | 5     |  | 5    | 0   | 0      |
-- | blue   | 0     |  | 0    | 0   | 0      |
-- +--------+-------+  +------+-----+--------+

-- Then the SUM does its works and voilá
-- In JSON: {"blue":6,"red":16,"yellow":8}

-- And what about COUNT? The same case, just replace SUM
SELECT
    COUNT(CASE sc.type WHEN 'blue' THEN sc.value ELSE 0 END) AS blue_count,
    COUNT(CASE sc.type WHEN 'red' THEN sc.value ELSE 0 END) AS red_count,
    COUNT(CASE sc.type WHEN 'yellow' THEN sc.value ELSE 0 END) AS yellow_count
FROM selected_colors AS sc;

-- And what about AVG? We would need to replace 0 with NULL
-- because in an average a zero represents a value:
-- AVG(5, 0, 5)    = 5 + 0 + 5 / 3 = 3.33
-- AVG(5, NULL, 5) = 5 + 5 / 2     = 5
SELECT
    COUNT(CASE sc.type WHEN 'blue' THEN sc.value ELSE NULL END) AS blue_avg,
    COUNT(CASE sc.type WHEN 'red' THEN sc.value ELSE NULL END) AS red_avg,
    COUNT(CASE sc.type WHEN 'yellow' THEN sc.value ELSE NULL END) AS yellow_avg
FROM selected_colors AS sc;

-- And what if we need other criteria or more operations?
-- Just modify the CASE
SELECT
    COUNT(CASE WHEN sc.type = 'blue' AND sc.type > 0 THEN THEN sc.value * 2 ELSE NULL END) AS blue,
    COUNT(CASE WHEN sc.type = 'red' AND sc.type > 0 THEN THEN sc.value * 2 ELSE NULL END) AS red,
    COUNT(CASE WHEN sc.type = 'yellow' AND sc.type > 0 THEN THEN sc.value * 2 ELSE NULL END) AS yellow
FROM selected_colors AS sc;