Merging multiple rows into one

Question

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

Data Values are different and they are TEXT, while x values are always represented as x
There will be various dispatch | driver_id | name | phone combinations

so there will be no different records with a different value for tuesday? for the same driver, name and phone? — Jeremy C.
– Jeremy C., Commented May 11, 2015 at 10:03
@Strawberry, this is correct. I show x where there is no data — Andrew
– Andrew, Commented May 11, 2015 at 10:15
I think you are going to need to self join your table 7 times (1 for every day of the week) and then write a where clause that makes sure you get the correct data :p — Jeremy C.
– Jeremy C., Commented May 11, 2015 at 10:20

fthiella · Accepted Answer · 2015-05-11 10:20:32Z

2

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.

answered May 11, 2015 at 10:20

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging multiple rows into one

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related