MYSQL - Rown to Columns many data without insert or looping

Question

I tried to search post. But i cant found it

I have table like this:

   +--------+----------+-----------+
   |   id   |   data1  |   data2   |
   +--------+----------+-----------+
   |  A123  |    A     |    315    |
   +--------+----------+-----------+
   |  A123  |    A     |    332    |
   +--------+----------+-----------+
   |  A123  |    A     |    220    |
   +--------+----------+-----------+
   |  C234  |    B     |    356    |
   +--------+----------+-----------+
   |  C234  |    B     |    224    |
   +--------+----------+-----------+
   |  C234  |    B     |    114    |
   +--------+----------+-----------+

I found like this:

   +--------+----------+-----------+-----------+
   |   id   |   data1  |        newdata        | 
   +--------+----------+-----------+-----------+
   |  A123  |    A     |       315,332,220     |
   +--------+----------+-----------+-----------+
   |  C234  |    B     |       356,224,114     |
   +--------+----------+-----------+-----------+

How do query the database something like:

   +--------+----------+-----------+-----------+-----------+
   |   id   |   data1  |  newdata1 |  newdata2 |  newdata3 |
   +--------+----------+-----------+-----------+-----------+
   |  A123  |    A     |    315    |    332    |    220    |
   +--------+----------+-----------+-----------+-----------+
   |  C234  |    B     |    356    |    224    |    114    |
   +--------+----------+-----------+-----------+-----------+

I just have 3 newdata (newdata1, newdata2, newdata3), no more

Thanks

Will it be always three records per `Id` what should happen when a `Id` say `D254` is having 4 records ? — Pரதீப், Jan 14 '17 at 11:30
`GROUP_CONCAT` would be much less of a headache if you have dynamic numbers of rows per `data1` value as @GurV mentioned. — Tim Biegeleisen, Jan 14 '17 at 11:32
Less of a headache still: handle issues of data display in application code — Strawberry, Jan 14 '17 at 11:34
Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Vasily Liaskovsky, Jan 14 '17 at 13:16

score 0 · Answer 1 · edited May 23 '17 at 12:08

What you are looking for is often called 'horizontal pivoting', where a number of values, in several rows, belonging to a common identifier in the table, with a fix maximum number n of rows per identifier, is pivoted from 'vertical' to n columns horizontally.

I just answered a very similar questions the other day.

You will see that is based on a GROUP BY of the common identifier, and works with a SUM(CASE WHEN .. END) in case of measures, and, for example, a MAX(CASE WHEN ... END) in case of not-summable values. Here's my previous post:

How to go about a column with different values in a same row in sql?

Should you need something more specific matching exactly your example, just shout.

Happy playing -

Marco the Sane

MYSQL - Rown to Columns many data without insert or looping

1 Answers1