SQL how to display group by results in columns

user_13876564 0
Viewed 42

I used a group by by id and year in a SQL query to display the following table :

id year nb
1 2018 10
2 2018 3
3 2019 108
2 2019 873
2 2020 42
1 2019 53
3 2018 423

Here is the SQL code that allowed me to get this table :

SELECT 
    id,
    year,
    COUNT(DISTINCT id) 
FROM 
    "data"
GROUP BY
    id, year

But, I want to display the result by columns, like the following table

id nb_2018 nb_2019 nb_2020
1 10 53 0
2 3 873 42
3 423 108 0

how I can turn the grouping by year into columns ?

multiple-columns count group-by sql
1 Answers

Does this answer your question?

SELECT id,
[2018] AS nb_2018,
[2019] AS nb_2019,
[2020] AS nb_2020
FROM
(
  SELECT *
  FROM tableA
)AS Source
PIVOT
(sum(nb)
FOR year in ([2018], [2019], [2020])
)as pvtable

db fiddle

user_187495140
Related