MySQL - Combine rows into one

Question

I searched through various questions but I didn't find an answer. I have a MySQL table 'teams':

+--+----+------+
|id|name|active|
+--+----+------+
|1 |bla |1     |
|2 |blu |0     |
|3 |croc|1     |
|4 |bold|1     |
|5 |foo |1     |
|6 |bar |0     |
+--+----+------+

and a table 'data':

+----+-------+----+
|team|project|time|
+----+-------+----+
|2   |some   |1   |
|2   |some2  |5   |
|3   |one    |16  |
|3   |one2   |100 |
|5   |more   |2   |
|5   |more2  |60  |
+----+-------+----+

In PHP I'm querying the data like this:

SELECT t.name, t.active, d.time
FROM teams t
RIGHT JOIN data d ON (t.id = d.team)
ORDER BY t.id

Everything works find and my result looks like this:

+----+------+----+
|name|active|time|
+----+------+----+
|blu |0     |1   |
|blu |0     |5   |
|croc|1     |16  |
|croc|1     |100 |
|foo |1     |2   |
|foo |1     |60  |
+----+------+----+

...but I want that my result looks like this:

+----+------+---+---+
|name|active|t1 |t2 |
+----+------+---+---+
|blu |0     |1  |5  |
|croc|1     |16 |100|
|foo |1     |2  |60 |
+----+------+---+---+

So... How can I reach this? I hope I have not overlooked an already asked question... :(

Thank you very much!

EDIT: First, thank you all, but I forgot something... It is also possible that e.g. one2 does not exist...

you don't. you want what's basically a pivot table, which mysql doesn't support. Do that transform in your client code instead. It CAN be written in sql, but it gets VERY ugly, VERY fast. — Marc B
– Marc B, Commented Apr 28, 2013 at 15:46
Pivot table basics: rows to columns, although I'd have to agree with @MarcB: it can get messy! — Marty McVry
– Marty McVry, Commented Apr 28, 2013 at 15:50

echo_Me · Accepted Answer · 2013-04-28 16:16:52Z

2

try this

 SELECT t.name, t.active, 
 min(time) t1, max(time)t2
FROM teams t
RIGHT JOIN data d ON (t.id = d.team)
group by name
ORDER BY t.id

DEMO HERE

EDIT:

try this

   SELECT t.name, t.active, 
   min(time) t1, if  (  max(time) is not null and max(time) <> min(time) , max(time), null )t2
  FROM teams t
  RIGHT JOIN data d ON (t.id = d.team)
  group by name
  ORDER BY t.id

DEMO HERE

edited Apr 28, 2013 at 16:16

answered Apr 28, 2013 at 15:58

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

7 Comments

echo_Me Over a year ago

he have already mentioned two records in his wished results t1 and t2 , if he have three he should mention also t3

echo_Me Over a year ago

@user2329363 and if one2 doesnt exist what result you want ??

user2329363 Over a year ago

It is possible, that the project 'one' exists, but the project 'one2' not. In this case 't2' should be null. But in your case 't2' has the value of 't1'.

user2329363 Over a year ago

echo_me Now I have another problem... It is also possible that 'one' doesn't exist but 'one2' does. Then 't1' should be null. How can I manage this?

echo_Me Over a year ago

my query is also working for this second suggestion, otherwise how you know if its t1 or t2 time value ?

|

fthiella · Accepted Answer · 2013-04-28 16:35:21Z

0

If there cold be at maximum two rows for each team, I would use something like this:

SELECT
  teams.name,
  teams.active,
  min(d1.time) t1,
  max(d2.time) t2
FROM
  teams INNER JOIN data d1
  ON teams.id=d1.team
  LEFT JOIN data d2
  ON teams.id=d2.team AND d1.time<d2.time
GROUP BY
  teams.name,
  teams.active

answered Apr 28, 2013 at 16:35

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Collectives™ on Stack Overflow

MySQL - Combine rows into one

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related