Pivoting a key-attrbute-value (plus metadata) table based on value of metadata-CodePudding

I have a Key-Attribute-Value table in a Microsoft SQL Server that has some additional metadata around when an attribute changed and when that record entered the database. I know all of the attributes I want to pivot on, and I know I want to aggregate by MAX(EnteredAt), but I don't know how to return a single record for each Entity ID that contains the most recent value for that attribute (some attrs can be null for an entity).

Sample Data:

EntityID | Attribute  | Value   | ChangedAt | EnteredAt |
---------------------------------------------------------
1        | AAttribute |  'abc'  | 1/1/2020  | 1/1/2020  |
1        | BAttribute |  'xyz'  | 1/1/2020  | 1/2/2020  |
1        | AAttribute |  '123'  | 1/2/2020  | 1/3/2020  |
1        | BAttribute |  'asd'  | 1/3/2020  | 1/3/2020  |

SQL Query I've tried:

SELECT EntityID -- we want this to be the Identity column for our output
    ,[AAttribute] as 'AAttribute'
    ,[BAttribute] as 'BAttribute'
FROM(
    SELECT [EntityID]
        ,[Attribute]
        ,[Value]
        ,[ChangedAt]
        ,[EnteredAt] 
    FROM [dbo].[Entity]) p
PIVOT
(
    MAX(EnteredAt)
    FOR Attribute IN
    ([AAttribute]
    ,[BAttribute]
    )
) AS pvt
order by pvt.ID

What I get from this is a table with my attributes as columns, but I get multiple values for each Attribute Column and multiple entries per attribute for each ID

Desired Output:

EntityID | AAttribute | BAttribute |
------------------------------------
1        | '123'      | 'asd'      |

I want to use something like a MAX(EnteredAt) AND MAX(ChangedAt WHERE ChangedAt < GETDATE()) for the 'Aggregation' piece. Is PIVOT the wrong tool for this?

CodePudding user response：

You can do this with the help of row_number and a little conditional aggregation:

select entityId,
  Max(case when Attribute = 'AAttribute' then value end) AAttribute,
  Max(case when Attribute = 'BAttribute' then value end) BAttribute
from (
  select *, Row_Number() over(partition by EntityID, Attribute order by EnteredAt desc) rn
  from t
)t
where rn = 1
group by EntityID;