0

From this sort of table:

| Timestamp | name  | value |

| 12:01:01  | foo   |  10   |
| 12:01:01  | Bob   |  11   |
| 12:01:01  | test  |  12   |

| 13:02:05  | foo   |  21   |
| 13:02:05  | Bob   |  20   |
| 13:02:05  | test  |  29   |

| 15:03:06  | foo   |  31   |
| 15:03:06  | Bob   |   1   |
| 15:03:06  | test  |  29   |

...

How can i make a SELECT statement that returns like this for more names

| timestamp | foo   | Bob | test | ....
|  12:01:01 |  10   |  11 |  12  |
|  13:02:05 |  21   |  20 |  29  |
|  15:03:06 |  31   |   1 |  29  |
   ...
Dharman
  • 30,962
  • 25
  • 85
  • 135
ndelucca
  • 517
  • 1
  • 8
  • 20
  • I've tryed with using CASE WHEN and using the same table with different aliases, but i can't get it to solve the problem dynamically, i can do it if i already know the exact names im looking for, but i'm trying to have that part solved here in the query. so if there are more names than "foo, bob and test", the query remains the same, and the output changes – ndelucca Jul 12 '17 at 20:47
  • @ndelucca show your current query. From your comment it sounds like you are headed down the right path with CASE WHEN; but you should not need multiple aliases for the **table**. _In any case, if the number of names you have is not predetermined, you will not find a singular "crosstab/pivot" query for it; at best you may be able to automate construction or a dynamic prepared query._ – Uueerdo Jul 12 '17 at 20:56
  • @Uueerdo today i asked this in a wrong way in another post, but it got me a good solution for a static number of names. Here is the post https://stackoverflow.com/questions/45058043/mysql-create-columns-from-rows, i implemented it as the answer marked as correct. – ndelucca Jul 12 '17 at 21:14
  • @ndelucca that probably is not the best answer; you're probably better off with a variant of the answer of the "duplicate question"; conditional aggregation is likely much more efficient than repeated joins. – Uueerdo Jul 12 '17 at 21:18

0 Answers0