SQL Pivoting with CASE Statement Not Working

Question

I am using MySQL, that is why I cannot use a PIVOT statement. So I am trying to learn the old way of pivoting, using the CASE statement. But something is not working. To recreate my table:

CREATE TABLE pivot_teste (
    Data DATE,
    UserId TEXT,
    CPF TEXT
);
INSERT INTO pivot_teste VALUES ("2021-06-02", "Joao", "297.386.970-69");
INSERT INTO pivot_teste VALUES ("2021-06-04", "Joao", "831.195.710-08");
INSERT INTO pivot_teste VALUES ("2021-06-01", "Joao", "791.843.660-10");
INSERT INTO pivot_teste VALUES ("2021-06-03", "Joao", "631.421.000-32");
INSERT INTO pivot_teste VALUES ("2021-06-01", "Maria", "297.386.970-69");
INSERT INTO pivot_teste VALUES ("2021-06-02", "Maria", "511.317.900-06");
INSERT INTO pivot_teste VALUES ("2021-06-05", "Geovanna", "096.850.790-56");
INSERT INTO pivot_teste VALUES ("2021-06-01", "Julia", "297.386.970-69");
INSERT INTO pivot_teste VALUES ("2021-06-01", "Eduardo", "297.386.970-69");
INSERT INTO pivot_teste VALUES ("2021-06-01", "Geovanna", "297.386.970-69");

The table in this example, have the following format:

Data        UserId    CPF
2021-06-02  Joao      297.386.970-69
2021-06-04  Joao      831.195.710-08
2021-06-01  Joao      791.843.660-10
2021-06-03  Joao      631.421.000-32
2021-06-01  Maria     297.386.970-69
2021-06-02  Maria     511.317.900-06
2021-06-05  Geovanna  096.850.790-56
2021-06-01  Julia     297.386.970-69
2021-06-01  Eduardo   297.386.970-69
2021-06-01  Geovanna  297.386.970-69

I am trying to get the following result:

Data        Joao            Maria           Geovanna        Julia           Eduardo
2021-06-02  297.386.970-69  511.317.900-06  NULL            NULL            NULL
2021-06-04  831.195.710-08  NULL            NULL            NULL            NULL
2021-06-01  791.843.660-10  297.386.970-69  297.386.970-69  297.386.970-69  297.386.970-69
2021-06-03  631.421.000-32  NULL            NULL            NULL            NULL
2021-06-05  NULL            NULL            096.850.790-56  NULL            NULL

In this moment, I have the query below. But for some reason it produces the correct results only for UserId = "Joao". And only the first value for UserId = "Geovanna" is returned. The remaining UserId's are filled with NULL's.

SELECT Data,
CASE WHEN UserId = 'Joao' THEN CPF END AS Joao,
CASE WHEN UserId = 'Maria' THEN CPF END AS Maria,
CASE WHEN UserId = 'Geovanna' THEN CPF END AS Geovanna,
CASE WHEN UserId = 'Eduardo' THEN CPF END AS Eduardo,
CASE WHEN UserId = 'Julia' THEN CPF END AS Julia
FROM pivot_teste
GROUP BY Data;

The result of this query:

Data        Joao            Maria  Geovanna        Eduardo  Julia
2021-06-02  297.386.970-69  NULL   NULL            NULL     NULL
2021-06-04  831.195.710-08  NULL   NULL            NULL     NULL
2021-06-01  791.843.660-10  NULL   NULL            NULL     NULL
2021-06-03  631.421.000-32  NULL   NULL            NULL     NULL
2021-06-05  NULL            NULL   096.850.790-56  NULL     NULL

Do you know what is wrong with this query? How would you pivot this pivot_teste table?

Does this answer your question? [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — , Aug 01 '21 at 16:57
Not exactly, but, is an interesting idea. Besides, @Gordon Linoff already gave the answer. I just forgot that I can use the `MAX()` function over text inputs. Thank you! — Pedro Faria, Aug 01 '21 at 17:01
(My point really was that this question has been answered thousands of times - the duplicate thing doesn't work as well as it should) — , Aug 01 '21 at 17:09

score 0 · Accepted Answer · answered Aug 01 '21 at 16:54

You need an aggregation functions, such as max():

SELECT Data,
       MAX(CASE WHEN UserId = 'Joao' THEN CPF END) AS Joao,
       MAX(CASE WHEN UserId = 'Maria' THEN CPF END) AS Maria,
       MAX(CASE WHEN UserId = 'Geovanna' THEN CPF END) AS Geovanna,
       MAX(CASE WHEN UserId = 'Eduardo' THEN CPF END) AS Eduardo,
       MAX(CASE WHEN UserId = 'Julia' THEN CPF END) AS Julia
FROM pivot_teste
GROUP BY Data;

SQL Pivoting with CASE Statement Not Working

1 Answers1