SQL UNION ALL query returning all Null values in date field

Question

I am trying to split one datefield column into three target columns, depending on a status column:

My table1 is as follows:

**id    status    DateField**
   1     A       1/5/2018
   2     B       1/6/2018
   3     C       1/7/2018

So I am trying to convert this data to the following format (Timestamps tracking when an id entered a certain stage)

id    Timestamp_A  Timestamp_B    Timestamp_C
1      1/5/2018     Null           Null
2.     Null         1/6/2018       Null
3.     Null         Null           1/7/2018

I used the following query to get the desired results

SELECT id, status,
 CAST( DATEFIELD AS DATE) AS "Timestamp_A",
 CAST(NULL AS DATE) AS "Timestamp_B",
 CAST(NULL AS DATE) AS "Timestamp_C"
FROM table 1
WHERE status='A'

UNION ALL

SELECT id, status,
 CAST( DATEFIELD AS DATE) AS "Timestamp_B",
 CAST(NULL AS DATE) AS "Timestamp_A",
 CAST(NULL AS DATE) AS "Timestamp_C"
FROM table 1
WHERE status='B'

UNION ALL

SELECT id, status,
 CAST( DATEFIELD AS DATE) AS "Timestamp_C",
 CAST(NULL AS DATE) AS "Timestamp_A",
 CAST(NULL AS DATE) AS "Timestamp_B"
FROM table 1
WHERE status='C'

Although each query works individually, when I use UNION ALL only the first query seems to work with respect to datefields i.e only Timestamp_A is populated with the correct values, while Timestamps B and C are all Nulls.

id    Timestamp_A  Timestamp_B    Timestamp_C
1      1/5/2018     Null           Null
2.       Null       Null           Null
3.       Null       Null           Null

Any input or direction on why this is happening?

Postgres. I am connecting it to tableau and using custom SQL to change the layout — data_analyst, Sep 24 '19 at 20:33
The issue is that your columns are not in order. You have to keep the order the same between each code block in the union — kjmerf, Sep 24 '19 at 20:34
oh that makes sense!thanks.So timestamps should be in order.Let me try that — data_analyst, Sep 24 '19 at 20:36
Great, I'm glad. Since you're new, please check out https://stackoverflow.com/help/someone-answers — kjmerf, Sep 24 '19 at 20:39

score 3 · Accepted Answer · answered Sep 24 '19 at 20:36

The order of your columns is the issue. You have to keep the order the same... But then again you don't really need the unions at all:

SELECT id
    , CASE WHEN DateField = '1/5/2018' THEN DateField END AS TimeStampA
    , CASE WHEN DateField = '1/6/2018' THEN DateField END AS TimeStampB
    , CASE WHEN DateField = '1/7/2018' THEN DateField END AS TimeStampC
FROM t

score 2 · Answer 2 · answered Sep 24 '19 at 21:37

2

I believe you want:

SELECT id, status,
       (CASE WHEN status = 'A' THEN datefield END) AS Timestamp_A,
       (CASE WHEN status = 'B' THEN datefield END) AS Timestamp_B,
       (CASE WHEN status = 'C' THEN datefield END) AS Timestamp_C
FROM t;

answered Sep 24 '19 at 21:37

Gordon Linoff

1,242,037
58
646
786

SQL UNION ALL query returning all Null values in date field

2 Answers2