sql - Flattening mutliple Rows into a single row based on identifier

Question

I'm trying to get a result set (and eventually create a view) that will combine rows based on an identifier. Below is an example of what I have, and what I'm trying to achieve:

Existing Data

    /* +----+------+---------+------+-------------------+-------------------+ */
    /* | ID | GUID |  Name   | Age  |      Address      | Entry_Create_Date | */
    /* +----+------+---------+------+-------------------+-------------------+ */
    /* |  1 |  123 | Bob     | 42   | NULL              | 1/2/2018          | */
    /* |  1 |  245 | Bob     | 41   | 123 Muffin Lane   | 1/1/2018          | */
    /* |  1 |  356 | Bob     | NULL | NULL              | 1/3/2018          | */
    /* |  2 |  456 | Charles | 21   | 235 Sherbert Lane | 1/3/2018          | */
    /* +----+------+---------+------+-------------------+-------------------+ */

Desired output:

    /* +----+---------+-----+-------------------+ */
    /* | ID |  Name   | Age |      Address      | */
    /* +----+---------+-----+-------------------+ */
    /* |  1 | Bob     |  42 | 123 Muffin Lane   | */
    /* |  2 | Charles |  21 | 235 Sherbert Lane | */
    /* +----+---------+-----+-------------------+ */

Results use the Entry_Create_Date to determine the latest record. Once that row is found, it uses that as the basis. If the values are NULL, it uses the values of the other rows with the same ID value to get the values from them. Precedence of column value is determined by the date it was created.

标签： sqltsql

Answer 1

This is complicated. You seem to want the most recent value from each field. How about . . .

select distinct id, name,
       first_value(age) over (partition by id order by case when age is not null then 1 else 2 end, Entry_Create_Date desc) as age,
       first_value(Address) over (partition by id order by case when Address is not null then 1 else 2 end, Entry_Create_Date desc) as Address
from t;