CODEWITHSUNDEEP
mysqlsqldatabasesortingselect
kaybuzz
kaybuzz

Reputation: 117

SQL is there a way to sort multiple time columns into one while preserving metadata?

I have a SQL database table that has four columns with different times. I want to be able to sort them in a way that I can merge each of a, b, c, and d timestamps into one column while preserving the id and metadata.

id | a_time | b_time | c_time | d_time | metadata1 | metadata2
1  | 8      | 7      | 2      | 4      | a         | b
2  | 6      | 1      | 12     | 10     | c         | d
3  | 5      | 9      | 3      | 11     | e         | f

In the above example, I would like something resulting in:

enter image description here

Upvotes: 2

Views: 64

Answers (2)

Mureinik
Mureinik

Reputation: 311088

You could union between several queries on the different times:

(SELECT id, a_time AS time, metadata1, metadata2
 FROM   mytable)
UNION
(SELECT id, b_time AS time, metadata1, metadata2
 FROM   mytable)
UNION
(SELECT id, c_time AS time, metadata1, metadata2
 FROM   mytable)
UNION
(SELECT id, d_time AS time, metadata1, metadata2
 FROM   mytable)
ORDER BY time

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You are looking to unpivot your data. MySQL doesn't have much built in support for this, but an ANSI way to do this would be to use UNION:

SELECT id, a_time AS time, metadata1, metadata2 FROM yourTable
UNION ALL
SELECT id, b_time, metadata1, metadata2 FROM yourTable
UNION ALL
SELECT id, c_time, metadata1, metadata2 FROM yourTable
UNION ALL
SELECT id, d_time, metadata1, metadata2 FROM yourTable
ORDER BY time;

enter image description here

Demo

Upvotes: 2

Related Questions