SQL: Most recent attribute set and date from multiple rows

Question

I have a table of several incomplete records each alluding to a certain customer - customer type (Type), annual purchases (A), customer location (B), secondary contractor (C), frequency (D), etc. These records act as snapshots of imperfect information about each customer and are assigned a year and unique snapshotID (snapID).

My goal is to create a single record composed of the latest non-zero, non-null values of each field (along with the year associated with that snapshotID) per customer and customer type (each customerID may have multiple customer types), and to be blank otherwise.

At different points during my exploration, I believed that the solution had to do with Partition, Subqueries, Pivots, Case, or simple Order By/Group By’s, but I am too much of a novice to make sense of differing use cases.

Sample Data:

[custID]    [Type]      [snapID]    [A]     [B]         [C]          [D]        [Year]
1025        InPerson    5           0       (NULL)      Cavs        Weekly      2020
1025        InPerson    6           275     (NULL)      Heat        Weekly      2019
1025        InPerson    7           600     Cleveland   Cavs        (NULL)      2018
7256        Online      8           (NULL)  Akron       (NULL)      (NULL)      2020
7256        InPerson    9           375     (NULL)      Nets        (NULL)      2020
7256        Online      10          400     (NULL)      Thunder     Monthly     2015
7256        InPerson    11          (NULL)  (NULL)      Magic       (NULL)      2014

Desired Result:

[custID]    [Type]      [A] [AYear] [B]         [BYear] [C]     [CYear] [D]     [DYear]
1025        InPerson    275 2019    Cleveland   2018    Cavs    2020    Weekly  2020
7256        InPerson    375 2020    (NULL)      (NULL)  Nets    2020    (NULL)  (NULL)
7256        Online      400 2015    Akron       2020    Thunder 2015    Monthly 2014

Answer 1

等待大神解答