Query mysql rows for columns

Asked

Viewed 187 times

0

Hi, I know there are some similar questions here already, but none of them helped me. I have the following table in my database:

+----------+---------------+--------+
| id_bolao | data_concurso | premio |
+----------+---------------+--------+
|   1      | 2019-05-18    |   1    |
|   1      | 2019-05-18    |   10   |
|   1      | 2019-05-18    |   22   |
|   1      | 2019-05-18    |   45   |
|   1      | 2019-05-18    |   70   |
|   1      | 2019-05-19    |   3    |
|   1      | 2019-05-19    |   4    |
|   1      | 2019-05-19    |   47   |
|   1      | 2019-05-19    |   34   |
|   1      | 2019-05-19    |   80   |
+----------+---------------+--------+

And I need to arrive at the following result, but these values are not fixed. every day will be registered new contests with 5 different awards:

+--------+---------------+---------+---------+---------+---------+---------+
|id_bolao| data_concurso | premio1 | premio2 | premio3 | premio4 | premio5 |
+--------+---------------+---------+---------+---------+---------+---------+
|   1    | 2019-05-18    |   1     |    10   |    22   |    45   |   70    |
|   1    | 2019-05-19    |   3     |    4    |    47   |    34   |   80    |
+--------+---------------+---------+---------+---------+---------+---------+

The questions already existing in the community give solutions with constant values and not dynamic values. Any idea?

mysql sql query

  • Before anything, start by doing the tour to understand how the community works; then read the How to ask to check how you can improve your question and finally access help center to check various articles that will help you better understand the site.

  • I already read the tour friend, and I believe the question is very clear! Thank you

  • 1

    Possible duplicate of PIVOT - Group Duplicate Records in a Single Line

  • Related: PIVOT - SELECT reversing row and column.

  • What version of MySQL?

  • The system will work as follows, every day will be registered new contests with 5 different prizes, but when showing the query on the screen would not like to show in several lines the prizes of the same contest, but rather all prizes in the same line, as the example.

  • Version of Mysql 5.7

Show 2 more comments

1 answer

0


I managed to solve my problem above by adding another field: sequencia in my table, it is a field that will always be 1 to 5 in the contests launched every day. The query was like this:

SELECT
   id_bolao,
   data_concurso,
   max(CASE sequencia WHEN 1 THEN premio ELSE 0 END) as premio1,
   max(CASE sequencia WHEN 2 THEN premio ELSE 0 END) as premio2,
   max(CASE sequencia WHEN 3 THEN premio ELSE 0 END) as premio3,
   max(CASE sequencia WHEN 4 THEN premio ELSE 0 END) as premio4,
   max(CASE sequencia WHEN 5 THEN premio ELSE 0 END) as premio5
FROM 
   bolao_concurso
GROUP BY 
   data_concurso;

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.