CODEWITHSUNDEEP
mysqlsplitrowsoql
Marc VILLAIN
Marc VILLAIN

Reputation: 21

SOQL - Return rows with one date column from rows with multiple date columns

Problem

I have this child table:

+------+------------+------------+
| Name |   Date_A   |   Date_B   | 
+------+------------+------------+
| John | 2017-08-13 | 2017-08-14 |
| Mary | 2017-08-12 | 2017-08-15 |
+------+------------+------------+

And I would like to get this result:

+------+------------+
| Name |    Date    |
+------+------------+
| Mary | 2017-08-12 |
| John | 2017-08-13 |
| John | 2017-08-14 |
| Mary | 2017-08-15 |
+------+------------+

Research

I've been searching for a few hours now and all I can find is complicated row split with functions or solutions to merge multiple rows into one but this is the complete opposite of my problem.

I've alwready looked into solutions using UNION ALL, GROUP BY, ORDER BY, CONNECT BY and INNER JOIN but none of them fitted my needs.

I am sure that the solution is simple but I can't find the right query !

Notes

The query must contain simple soql and only call the database once for it to work.

EDIT: The UNION keyword isn't supported by SOQL unfortunately :(

EDIT: Solution

It seems there is no solution to this problem using SOQL but to make multiple requests and manually merge the results.

With SQL, we would only have to use UNION (as in the answers below) but this isn't supported by SOQL for now.

Upvotes: 1

Views: 375

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

One option uses a UNION:

SELECT Name, Date_A AS Date
FROM yourTable
UNION ALL
SELECT Name, Date_B
FROM yourTable

The trick here is to stack the two date columns (with their associated names) on top of each other.

Upvotes: 2

Related Questions