Can I make pivot table in SQL

Question

I've my data like this:

│ Row │ variable │ value │ Customer │ location │
├─────┼──────────┼───────┼──────────┼──────────┤
│ 1   │ January  │ 5     │ x        │ X1       │
│ 2   │ January  │ 3     │ x        │ X2       │
│ 3   │ January  │ 6     │ y        │ X3       │
│ 4   │ February │ 4     │ x        │ X1       │
│ 5   │ February │ 3     │ x        │ X2       │
│ 6   │ February │ 3     │ y        │ X3       │
│ 7   │ March    │ 5     │ x        │ X1       │
│ 8   │ March    │ 4     │ x        │ X2       │
│ 9   │ March    │ 4     │ y        │ X3       │
│ 10  │ April    │ 0     │ x        │ X1       │
│ 11  │ April    │ 2     │ x        │ X2       │
│ 12  │ April    │ 2     │ y        │ X3       │
│ 13  │ May      │ 3     │ x        │ X1       │
│ 14  │ May      │ 5     │ x        │ X2       │
│ 15  │ May      │ 5     │ y        │ X3       │
│ 16  │ June     │ 2     │ x        │ X1       │
│ 17  │ June     │ 1     │ x        │ X2       │
│ 18  │ June     │ 7     │ y        │ X3       │

Can I make an SQL that generate a pivot table from it as:

│ Row │ Customer │ January │ February │ March │ April │ May   │ June  │
├─────┼──────────┼─────────┼──────────┼───────┼───────┼───────┼───────┤
│ 1   │ x        │ 8       │ 7        │ 9     │ 2     │ 8     │ 3     │
│ 2   │ y        │ 6       │ 3        │ 4     │ 2     │ 5     │ 7     │

Along with the problem you should **include the code you have up until this point** (*make some effort to answer your problem/question as [so] is not a code writing service*). After [doing more research](http://meta.stackoverflow.com/questions/261592) if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a **[mcve]**. I suggest reading [*How do I ask a Good Question*](/help/how-to-ask) and [*Writing the Perfect Question*](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). — Igor, Oct 09 '19 at 21:16
Also when you added the SQL tag, a large box popped up and suggested that you also add a tag for the specific DBMS you're using, as features and syntax between them vary. Why did you ignore that suggestion? Now we have to go through the **What DBMS are you using?** Features and support vary between them, and knowing which one you're using is relevant. **Please add a tag for the DBMS** so we don't waste your time or ours answering only to find out it won't work on your specific database. — Igor, Oct 09 '19 at 21:16

score 0 · Accepted Answer · answered Oct 09 '19 at 21:17

Use conditional aggregation:

SELECT
    customer,
    SUM(CASE WHEN variable = 'January' THEN value ELSE 0 END) January,
    SUM(CASE WHEN variable = 'February' THEN value ELSE 0 END) February,
    SUM(CASE WHEN variable = 'March' THEN value ELSE 0 END) March,
    SUM(CASE WHEN variable = 'April' THEN value ELSE 0 END) April,
    SUM(CASE WHEN variable = 'May' THEN value ELSE 0 END) May,
    SUM(CASE WHEN variable = 'June' THEN value ELSE 0 END) June
FROM mytable
GROUP BY customer
ORDER BY customer

If you really need the first column, that basically assigns new numbers to the records, you can use ROW_NUMBER() (this feature is available in MySQL 8.0 only):

SELECT 
    ROW_NUMBER() OVER(ORDER BY customer) Row,
    x.*
FROM (
    SELECT
        customer,
        SUM(CASE WHEN variable = 'January' THEN value ELSE 0 END) January,
        SUM(CASE WHEN variable = 'February' THEN value ELSE 0 END) February,
        SUM(CASE WHEN variable = 'March' THEN value ELSE 0 END) March,
        SUM(CASE WHEN variable = 'April' THEN value ELSE 0 END) April,
        SUM(CASE WHEN variable = 'May' THEN value ELSE 0 END) May,
        SUM(CASE WHEN variable = 'June' THEN value ELSE 0 END) June
    FROM mytable
    GROUP BY customer
) x
ORDER BY customer

Thanks, what if I've so many fields, let's say `60`, shall I keep writting the `SUM(CASE WHEN variable = 'xxx' THEN value ELSE 0 END) xxx` for 60 times? — Hasan A Yousef, Oct 09 '19 at 21:22
@HasanAYousef: yes indeed, that is the way this type of query works... — GMB, Oct 09 '19 at 21:36
did u see this: https://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/ — Hasan A Yousef, Oct 09 '19 at 21:47
@HasanAYousef: yes, this is a different technique, that involves using dynamic SQL. Your question did not mentionned that the number of columns was dynamic, so I want for the approach based on aggregation... — GMB, Oct 09 '19 at 22:14

Can I make pivot table in SQL

1 Answers1