0

I'm trying to resolve some problem I have via SQL but currently, without any success, I have a historical player VIP level table that looks like that:

+----------+----------+----------+-------+--+
| PlayerID | OldLevel | NewLevel | Date  |  |
+----------+----------+----------+-------+--+
| 747      | Entry    | Bronze   | 03-17 |  |
| 747      | Bronze   | Silver   | 04-17 |  |
| 123      | Entry    | Bronze   | 03-17 |  |
| 123      | Bronze   | Silver   | 04-17 |  |
| 123      | Silver   | Gold     | 06-17 |  |
+----------+----------+----------+-------+--+

OldVip = Player VIP level Before the change Date (Date column)

NewVIP = Player VIP level After the change date (Date column)

I want to create a query that will create the following output:

+---------+-------+-------+--------+--------+--------+--------+--------+--------+--+
| ID/Date | 01-17 | 02-17 | 03-17  | 04-17  | 05-17  | 06-17  | 07-17  | 08-17  |  |
+---------+-------+-------+--------+--------+--------+--------+--------+--------+--+
| 747     | Entry | Entry | Bronze | Silver | Silver | Silver | Silver | Silver |  |
| 123     | Entry | Entry | Bronze | Silver | Silver | Gold   | Gold   | Gold   |  |
+---------+-------+-------+--------+--------+--------+--------+--------+--------+--+

Tried to use Cases in the SELECT statement but didn't get the desired result.

Any help is appreciated.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
Dima Kruchenetsky
  • 15
  • 4
  • 1
    Google for "pivot table MySQL," or search for the same on Stack Overflow. Of note, if you need an arbitrary date range, covering many months and years, then you might need dynamic SQL to handle that. – Tim Biegeleisen Aug 14 '17 at 11:33
  • Pls also note, while you can pivot the data within MySQL, it may be more efficient to perform this transformation in a traditional programming language / or analytics tool. Particularly if you have to create data that do not exists, such as 01-17 - 747 - entry – Shadow Aug 14 '17 at 11:44

0 Answers0