How to create a correct pivot table with this SQL query in SQLite-CodePudding

I've this hard SQL pivot table I'm trying to solve. I can't find the solution for it.


with cte as (
  select nationality, class as n
  from Fifaklasse
  group by nationality
)
select nationality,
  coalesce(sum(case when n = 'Wereldklasse' then n end), 0) as 'Wereldklasse',
  coalesce(sum(case when n = 'Topklasse'  then n end), 0) as 'Topklasse',
  coalesce(sum(case when n = 'Subtop'  then n end), 0) as 'Subtop'
from cte
group by nationality

player	nationality	Class
Messi	n1	World_class
Aguero	n1	World_class
Vidal	n2	Top_class
Pinto	n3	Subtop_class
Ronaldo	n3	World_class
Suarez	n4	World_class
Falcao	n4	Top_class

RESULT

Nationality	World_class	Top_class	Subtop_class
n1	2	0	0
n2	0	1	0
n3	1	0	1
n4	1	1	0

CodePudding user response：

You should be summing the value 1 to obtain the counts. That aside, I would use this version with boolean expressions:

SELECT nationality,
       SUM(class = 'Wereldklasse') AS Wereldklasse,
       SUM(class = 'Topklasse') AS Topklasse,
       SUM(class = 'Subtop') AS Subtop
FROM Fifaklasse
GROUP BY nationality;

CodePudding user response：

Tim is correct. But to make it more understandable and work for most other database engines, this should be rewritten as

SELECT nationality,
   SUM(iif(klass = 'World', 1, 0)) AS Wereldklasse,
   SUM(iif (klass = 'Top', 1, 0)) AS Topklasse,
   SUM(iif (klass = 'Subtop', 1, 0)) AS Subtop
FROM Fifaklass
GROUP BY nationality