Greatest value in group solution that also meets requirements for indexed view

Question

In an Azure SQL database, I have an EAV-style table AttributeValues that stores multiple attribute values, like this:

Id	Organization_Id	Attribute_Id	Value
1	1	1	Old Org 1 Description
2	1	1	New Org 1 Description
3	2	1	Old Org 2 Description
4	2	1	New Org 2 Description
5	1	2	Old Org 1 Title
6	1	2	New Org 1 Title
7	2	2	Old Org 2 Title
8	2	2	New Org 2 Title

These values need to be pivoted into Organization-specific rows, like this, which include the latest attribute value of each type (i.e. the top Value, ordered by AttributeValues.Id DESC):

Organization_Id	Description	Title
1	New Org 1 Description	New Org 1 Title
2	New Org 2 Description	New Org 2 Title

The additional caveat here is that the working query needs to adhere to all the restrictions for an Indexed View, meaning no PIVOT, no CTEs or derived tables, no window functions, no subqueries, no outer joins, etc., because the Description and Title fields need to have full-text indexes applied in order to be used with CONTAINS() for search. Can this be done?

Answer 1

Seems like you just need a "top 1 per per group" and then PIVOT/conditional aggregate:

WITH RNs AS(
    SELECT Organization_Id,
           Attribute_Id,
           [Value],
           ROW_NUMBER() OVER (PARTITION BY Organization_Id,Attribute_Id ORDER BY Id DESC) AS RN
    FROM (VALUES(1,1,1,'Old Org 1 Description'),
                (2,1,1,'New Org 1 Description'),
                (3,2,1,'Old Org 2 Description'),
                (4,2,1,'New Org 2 Description'),
                (5,1,2,'Old Org 1 Title'),
                (6,1,2,'New Org 1 Title'),
                (7,2,2,'Old Org 2 Title'),
                (8,2,2,'New Org 2 Title'))V(Id,Organization_Id,Attribute_Id,Value))
SELECT Organization_Id,
       MAX(CASE Attribute_Id WHEN 1 THEN [Value] END) AS Description,
       MAX(CASE Attribute_Id WHEN 2 THEN [Value] END) AS Title
FROM RNs
WHERE RN = 1
GROUP BY Organization_Id;

As noted, however, you can't index a view on this. If you need an indexed view, you'll likely need to first normalise your design, and 2, look at history/temporal tables.