CODEWITHSUNDEEP
mysqlsql
Andrew
Andrew

Reputation: 7768

Merging multiple rows into one

I have a pretty complex query that pulls some data. The result set looks like this:

mon | tue  | wed | thu  | fri | sat  | sun |  dispatch | driver_id | name | phone
 x  |  x   |  x  |  x   |  x  | data |  x  |         1 |         2 | Bob  | 123
 x  | data |  x  |  x   |  x  |  x   |  x  |         1 |         2 | Bob  | 123
 x  |  x   |  x  | data |  x  |  x   |  x  |         1 |         2 | Bob  | 123

I am wondering if anyone can help me to merge multiple columns which have the same dispatch | driver_id | name | phone into ONE ROW

Therefore I will end up with this (All data values substituted x values):

mon | tue  | wed | thu   | fri | sat   | sun |  dispatch | driver_id | name | phone
  x | data | x   | data  | x   | data  | x   |         1 |         2 | Bob  | 123

Upvotes: 2

Views: 114

Answers (1)

fthiella
fthiella

Reputation: 49049

If "x" values are NULL, you can use a query like this:

SELECT
  MAX(mon) AS mon,
  MAX(tue) AS tue,
  MAX(wed) AS wed,
  MAX(thu) AS thu,
  MAX(fri) AS fri,
  MAX(sat) AS sat,
  MAX(sun) AS sun,
  dispatch,
  driver_id,
  name,
  phone
FROM
  yourtable
GROUP BY
  dispatch,
  driver_it,
  name,
  phone

but if "x" values are the exact string "x" then you shuld use something like this:

MAX(CASE WHEN mon!='x' THEN mon END) as mon,
MAX(CASE WHEN tue!='x' THEN tue END) as tue,
...

This will work, unless two or more different rows have different values for the same column.

Upvotes: 2

Related Questions