CODEWITHSUNDEEP
mysql
Gugis
Gugis

Reputation: 539

Separate one column to two by row's value

I have 2 tables - posts and meta

posts structure:

+---------+---------+
|   id    |  title  |
+---------+---------+

meta structure:

+---------+---------+---------+
|   post  |   key   |  value  |
+---------+---------+---------+

I need to select records where meta.key is "end" or "result", and goup by post.id (if there's both "end" and "result" meta keys)

Here's my try:

SELECT
posts.id,
posts.title,
meta.post,
meta.`value`,
CASE meta.`key` WHEN 'result' THEN value END AS 'result',
CASE meta.`key` WHEN 'end' THEN value END AS 'end'
FROM
posts
INNER JOIN meta ON posts.id = meta.post
WHERE meta.`key` IN ('result', 'end')

It returns two separate records one with meta.key "end" and one with "result".

+------------+------------+------------+------------+------------+------------+
|    id      |   title    |   post     |   value    |   result   |     end    |
+------------+------------+------------+------------+------------+------------+
|     1      |  Untitled  |      1     |     5      |    (Null)  | 1344240000 |
+------------+------------+------------+------------+------------+------------+
|     1      |  Untitled  |      1     |   88:79    |    88:79   |   (Null)   |
+------------+------------+------------+------------+------------+------------+

What I need is to merge those two records to one:

+------------+------------+------------+------------+------------+------------+
|    id      |   title    |   post     |   value    |   result   |     end    |
+------------+------------+------------+------------+------------+------------+
|     1      |  Untitled  |      1     |     -      |    88:79   | 1344240000 |
+------------+------------+------------+------------+------------+------------+

Upvotes: 3

Views: 725

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

As long as there's only one row for 'result' and one for 'end' per post id, you can just use MAX as an aggregate function with your original query and group by posts.id at the end; I can't quite figure out your logic for VALUE so I left it out for now;

SELECT posts.id, posts.title,
  MAX(CASE meta.`key` WHEN 'result' THEN value END) AS 'result',
  MAX(CASE meta.`key` WHEN 'end' THEN value END)  AS 'end'
FROM posts
INNER JOIN meta ON posts.id = meta.post
WHERE meta.`key` IN ('result', 'end')
GROUP BY posts.id, posts.title

An SQLfiddle to test with.

Upvotes: 0

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

You can do this by INNER JOINing meta twice, i.e.

SELECT

  p.id,
  p.title,
  mr.value AS result,
  me.value AS end

FROM posts AS p

INNER JOIN meta AS mr

  ON mr.post = p.id
  AND mr.`key` = 'result'

INNER JOIN meta AS me
  ON me.post = p.id
  AND me.`key` = 'end';

SQL fiddle link : http://sqlfiddle.com/#!2/2a89e/5

Upvotes: 1

Taryn
Taryn

Reputation: 247650

Since you are trying to pivot the data, you would typically use an aggregate function with the CASE:

SELECT
  posts.id,
  posts.title,
  -- meta.post,
  -- meta.`value`,
  max(CASE meta.`key` WHEN 'result' THEN value END) AS 'result',
  max(CASE meta.`key` WHEN 'end' THEN value END) AS 'end'
FROM posts
INNER JOIN meta 
  ON posts.id = meta.post
WHERE meta.`key` IN ('result', 'end')
GROUP BY posts.id, posts.title  -- , meta.post, meta.`value`

See SQL Fiddle with Demo

Note: You are showing two separate values for the value column, you will need to decide the logic to select a value because if those are distinct when the group by is performed you will get multiple rows. So you will see that I commented out those two columns since they are being used in the CASE and the post column is already displayed.

Upvotes: 0

Related Questions