how to get data in a single row in database search

Question

i want to to get the data from my database table games. i searched as

SELECT team_id,result from games where team_id=17;

In games table i have results of all matchs such as

id     team_id team    result
21       17    India   w
22       17    India   l
23       17    India   l
24       17    India   w

all i want to get this data as

team_id  result1 result2 result3 result4
  17      w      l      l       w

If anyone have idea how could i do it?

http://stackoverflow.com/questions/7674786/mysql-pivot-table — Web Artisan, May 24 '16 at 12:38

score 0 · Answer 1 · answered May 24 '16 at 12:36

For a limited number of results, say up to 4, you could do something like this:

SELECT team_id, 
       MAX(CASE WHEN rn = 1 THEN result END) AS result1, 
       MAX(CASE WHEN rn = 2 THEN result END) AS result2, 
       MAX(CASE WHEN rn = 3 THEN result END) AS result3, 
       MAX(CASE WHEN rn = 4 THEN result END) AS result4 
FROM (
  SELECT team_id, result,
         @row_number := @row_number + 1 AS rn
  FROM games 
  CROSS JOIN (SELECT @row_number := 0) AS vars
  WHERE team_id = 17
  ORDER BY id) AS t

Doesn't look like it is a limited amount of results.. – sagi May 24 '16 at 12:37 — sagi, May 24 '16 at 12:37

score 0 · Answer 2 · answered May 24 '16 at 12:45

0

You could try mysql group_concat to get all the results in a field with comas, maybe this helps you too...

answered May 24 '16 at 12:45

Caius

193
12

score 0 · Answer 3 · answered May 24 '16 at 12:47

A "dirty" (but rather general - in that it is not limited by the number of results) way of doing that would be:

SELECT team_id, GROUP_CONCAT(result) AS results FROM GAMES ...

So that results will contain w,l,l,w. Then (I noticed you tagged the post using 'php'), you may use something along the lines of explode() to get an array out of the result

$results = explode(',', $row[1]);

how to get data in a single row in database search

3 Answers3