CODEWITHSUNDEEP
mysql
Michel
Michel

Reputation: 4157

Get values of multiple columns each on it's own row

I have a table with 3 values:

id | name | v1   | v2   | v3 
---+------+------+------+---
 1 | John | 6    | null | 7
 2 | Jack | null | 5    | 9

Is there a simple way to get the different values from v1, v2 and v3 each on it's own row, preferably without the null's? So the result would be:

id | name | value
---+------+------
 1 | John |  6
 1 | John |  7
 2 | Jack |  5
 2 | Jack |  9

The only query I came up with is one with 3 subqueries, so maybe there is a better way to do this:

SELECT id, name, value 
FROM (
  (SELECT id, name, v1 value FROM table WHERE v1 IS NOT NULL)
   UNION ALL
  (SELECT id, name, v2 value FROM table WHERE v2 IS NOT NULL)
   UNION ALL
  (SELECT id, name, v3 value FROM table WHERE v3 IS NOT NULL)
) V
GROUP BY id, name, value

Upvotes: 2

Views: 27

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Obviously, if you start with a table design like this, then the problem becomes trivial...

| id | v_id | value |
+----+------+-------+
|  1 |    1 |     6 | 
|  1 |    3 |     7 |
|  2 |    2 |     5 | 
|  2 |    3 |     9 |

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

you don't need subquery and group by - just UNION ALL is enough

SELECT id, name, v1 value FROM table WHERE v1 IS NOT NULL
UNION ALL
SELECT id, name, v2 value FROM table WHERE v2 IS NOT NULL
UNION ALL
SELECT id, name, v3 value FROM table WHERE v3 IS NOT NULL

Upvotes: 1

Related Questions