0

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?

Thom A
  • 88,727
  • 11
  • 45
  • 75
coryseaman
  • 377
  • 4
  • 16
  • You might separate the concerns and use a history table for historical versions, or a Temporal Table. – David Browne - Microsoft Nov 05 '21 at 16:16
  • 3
    No - it won't be possible to construct a query that returns the desired results and meets the [many restrictions](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15) for an indexed view (e.g. Self-joins, OVER clause, Derived table, Subquery, MIN, MAX, APPLY, PIVOT) – Martin Smith Nov 05 '21 at 16:16

1 Answers1

0

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75