MYSQL - Selecting multiple rows in a single row

Question

I have a table in MYSQL named as permit_bills which contains columns as bill_no, alcohol_typ, origin, 2000ml, 1000ml, bill_date. Table is shown below:

    +---------+--------------+---------+--------+--------+-----------+
    | bill_no | alcohol_typ  | origin  | 2000ml | 1000ml | bill_date |
    +---------+------------- + --------+--------+--------+-----------+
    |  2001   |    s         |   f     |   2    |    1   |01-02-2017 |
    |  2001   |    m         |   w     |   3    |    4   |01-02-2017 |
    +---------+--------------+---------+--------+--------+-----------+

I want to select all rows from above table into a single row based on their bill_no and bill_date and want to display the columns of 2000ml and 1000ml as per their alcohol_typ and `origin.

My output table must be like this:

    +---------+--------------+-------------+------------+------------+-----------+
    | bill_no | s_f_2000ml   | s_f_1000ml  | m_w_2000ml | m_w_1000ml | bill_date |
    +---------+------------- + ------------+------------+------------+-----------+
    |  2001   |      2       |     1       |     3      |     4      |01-02-2017 |
    +---------+--------------+-------------+------------+------------+-----------+

You need PIVOT...take a look here http://stackoverflow.com/questions/7674786/mysql-pivot-table — Kostis, Feb 16 '17 at 11:01
If it was me, I'd handle this kind of thing in application code - much more scalable and flexible. — Strawberry, Feb 16 '17 at 11:02
I.e. do a GROUP BY, use case expressions to do conditional aggregation. — jarlh, Feb 16 '17 at 11:03

score 1 · Accepted Answer · answered Feb 16 '17 at 11:17

Try this (pivot) query -

SELECT
  bill_no,
  MAX(IF(alcohol_typ = 's' AND origin = 'f', `2000ml`, NULL)) AS s_f_2000ml,
  MAX(IF(alcohol_typ = 's' AND origin = 'f', `1000ml`, NULL)) AS s_f_1000ml,
  MAX(IF(alcohol_typ = 'm' AND origin = 'w', `2000ml`, NULL)) AS m_w_2000ml,
  MAX(IF(alcohol_typ = 'm' AND origin = 'w', `1000ml`, NULL)) AS m_w_1000ml,
  bill_date
FROM permit_bills
  GROUP BY bill_no, bill_date

score 1 · Answer 2 · answered Feb 16 '17 at 11:26

Are you sure your output table needs to look like that?

You may be able to use the GROUP_CONCAT function instead which is sometimes an amazingly useful tool. You will need to split or explode the values in your application, but it might be all you need.

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

MYSQL - Selecting multiple rows in a single row

2 Answers2