Simple PIVOT query throws error message

Question

I use the following SQL to get the visits from our database:

SELECT 
    t.country, MONTH(visit_date), t.visits
FROM
    visits t
WHERE 
    t.visit_date BETWEEN '2017-01-01' AND '2017-12-31'
ORDER BY 
    t.country

The result of this query looks like this:

   Country     Month      Visits
   -------------------------------
    DE           1        5.000
    DE           2        6.000
    :            :          :
    :            :          :
    US           1        5.400
    US           2        3.000
    :            :          :
    :            :          :

Allt this works fine so far.

Now I want to pivot the result so it looks like this in the end:

        1          2        3        4
DE    10.000    15.000   30.000    20.000
US    20.220    18.400   40.000    19.000

Therefore I tried to use the following SQL:

SELECT 
    *
FROM
    (SELECT t.country, MONTH(visit_date), t.visits
     FROM visits t
     WHERE t.visit_date BETWEEN '2017-01-01' AND '2017-12-31'
     ORDER BY t.country) AS s
PIVOT
    (SUM(t.visits)
     FOR MONTH(t.visit_date) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    ) AS pvt

However, this query throws an error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your DB server version for the right syntax to use near 'PIVOT ( SUM(t.unique_visits) FOR MONTH(t.visit_date) IN (1, 2, 3, 4, 5, 6, 7,' at line 7

Do you have any idea where there is a mistake in my query?

Answer 1

MySQL does not provide PIVOT function. You will have to build pivot table on your own. A commonly used way to do that is conditional aggregation.

You can change your query like this to get desired output.

SELECT 
    country,
    SUM(CASE WHEN month = 1 THEN visits END) AS '1',
    SUM(CASE WHEN month = 2 THEN visits END) AS '2',
    SUM(CASE WHEN month = 3 THEN visits END) AS '3',
    SUM(CASE  WHEN month = 4 THEN visits END) AS '4'
FROM
    (SELECT 
        t.country, MONTH(visit_date) month, t.visits
    FROM visits t
    WHERE t.visit_date BETWEEN '2017-01-01' AND '2017-12-31'
    ORDER BY t.country) AS s
GROUP BY country;

Answer 2

In MySQL you don't have PIVOT function.

In case you can use alternative ways with connect SUM values.

Good example you find in THIS LINK