Get back first non-null value grouped by some other columns-CodePudding

How to go from left to right?
Basically for each TIME-person combo, I need the first non-null value from the id columns.

CodePudding user response：

Aggregate by time and person and take the max of the other 4 columns:

SELECT
    time,
    person,
    MAX(id1) AS id1,
    MAX(id2) AS id2,
    MAX(id3) AS id3,
    MAX(id4) AS id4
FROM yourTable
GROUP BY
    time,
    person;

CodePudding user response：

Best way that I know of (i.e. in my experience of practical situations where this comes up) is to use PIVOT and UNPIVOT (may not be necessary in the example above, but if you have multiple rows with id1's for a time/person combo it provides a nice mechanism to do this).

Unfortunately how to do this depends on which SQL DBMS you're using. Below example will work for MS SQL Server. Oracle has similar functionality. MySQL/PostgreSQL I don't know that well, but I don't think they have PIVOT/UNPIVOT though you may find something similar if you search for "lateral derived table" or "cross join lateral":

SELECT [Time], Person, id1, id2, id3, id4
FROM    (SELECT [Time], Person, id, idval
            FROM (SELECT [Time], Person, id1, id2, id3, id4 FROM #test) pvt
            UNPIVOT (idval FOR id IN (id1, id2, id3, id4)) AS unpvt
        ) AS unpivoted
PIVOT (
        MIN(idval) FOR [id] IN ([id1], [id2], [id3], [id4])
    ) AS pivoted

For this example, I assumed that by the "first" non-null value you meant the one with the lowest ID, i.e. MIN(idval), but this may not be what you're after. There are ways to use other columns to determine which is first, e.g. if time should determine what "first" means then something like this could work:

SELECT [Time], Person, id1%1000000 'id1', id2%1000000 'id2', id3%1000000 'id3', id4%1000000 'id4'
FROM    (SELECT [Time], Person, id, idval (1000000*[Time]) 'idval'
            FROM (SELECT [Time], Person, id1, id2, id3, id4 FROM #test) pvt
            UNPIVOT (idval FOR id IN (id1, id2, id3, id4)) AS unpvt
        ) AS unpivoted
PIVOT (
        MIN(idval) FOR [id] IN ([id1], [id2], [id3], [id4])
    ) AS pivoted

Last resort, you can also to something like this (ugly as hell IMHO and not great for performance either):

SELECT tp.Person, tp.[Time], t_id1.id 'id1', t_id2.id 'id', t_id3.id 'id3', t_id4.id 'id4'
    FROM (SELECT [Time], Person FROM #Test t GROUP BY [Time], Person) tP
    LEFT JOIN (SELECT [Time], Person, MIN(id1) 'id' FROM #Test t WHERE t.id1 IS NOT NULL GROUP BY [Time],Person) t_id1 ON t_id1.Person = tP.Person
    LEFT JOIN (SELECT [Time], Person, MIN(id2) 'id' FROM #Test t WHERE t.id2 IS NOT NULL GROUP BY [Time],Person) t_id2 ON t_id2.Person = tP.Person
    LEFT JOIN (SELECT [Time], Person, MIN(id3) 'id' FROM #Test t WHERE t.id3 IS NOT NULL GROUP BY [Time],Person) t_id3 ON t_id3.Person = tP.Person
    LEFT JOIN (SELECT [Time], Person, MIN(id4) 'id' FROM #Test t WHERE t.id4 IS NOT NULL GROUP BY [Time],Person) t_id4 ON t_id3.Person = tP.Person