By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH attributes AS (
SELECT 'John' AS Name, 5543 AS attribute_1, 4523 AS attribute_2, 2554 AS attribute_3 UNION ALL
SELECT 'Paul', 4523, 5543, 9523 UNION ALL
SELECT 'Anna', 2554, 5543, 2554
)
SELECT
attribute AS strategy_id,
COUNT(CASE WHEN col = 1 THEN 1 END) AS attribute_1,
COUNT(CASE WHEN col = 2 THEN 1 END) AS attribute_2,
COUNT(CASE WHEN col = 3 THEN 1 END) AS attribute_3
FROM
(
SELECT attribute_1 AS attribute, 1 AS col FROM attributes
UNION ALL
SELECT attribute_2, 2 FROM attributes
UNION ALL
SELECT attribute_3, 3 FROM attributes
) t
GROUP BY
attribute;
| strategy_id | attribute_1 | attribute_2 | attribute_3 |
|---|---|---|---|
| 5543 | 1 | 2 | 0 |
| 4523 | 1 | 1 | 0 |
| 2554 | 1 | 0 | 2 |
| 9523 | 0 | 0 | 1 |