基于FetchXML的数据展示问题：关联数据列填充与双列呈现

Hey there! Let's break down your two questions step by step, since you're restricted to FetchXML and your specified tool (no SQL allowed):

1. Can we display/populate related data in predefined columns? #

Absolutely! FetchXML is built to pull related entity data using the <link-entity> element, which lets you include attributes from associated records directly in your result set.

For example, if you have a knight entity linked to a princess entity (1:N relationship), structure your FetchXML like this to pull both the knight's name and their associated princess's name:

<fetch>
  <entity name="knight">
    <attribute name="fullname" alias="knight_name" />
    <!-- Link to the related princess entity -->
    <link-entity name="princess" from="knight_id" to="knight_id" alias="related_princess">
      <attribute name="fullname" alias="princess_name" />
    </link-entity>
  </entity>
</fetch>

When setting up predefined columns in your tool (like Power Apps, Dynamics 365 Advanced Find, or Power BI), select the aliased attributes from the linked entity to populate those columns with related data. Just make sure to match the from and to attributes in <link-entity> to your actual relationship schema names.

2. How to split princess names into two columns (max 2 per knight) when grouping by knight? #

Since FetchXML doesn’t natively support pivoting rows to columns, you’ll need to combine FetchXML’s filtering capabilities with your tool’s data manipulation features to get this done. Here are two reliable approaches:

Option 1: Use dual <link-entity> elements in FetchXML #

Since each knight has at most two princesses, add two separate <link-entity> blocks—each targeting one princess, with filters to avoid duplicates. For example, if sorting by creation date:

<fetch>
  <entity name="knight">
    <attribute name="fullname" alias="knight_name" />
    <!-- First princess (oldest or first in sequence) -->
    <link-entity name="princess" from="knight_id" to="knight_id" alias="princess_1" top="1">
      <attribute name="fullname" alias="princess_name_1" />
      <order attribute="createdon" descending="false" />
    </link-entity>
    <!-- Second princess (exclude the first one) -->
    <link-entity name="princess" from="knight_id" to="knight_id" alias="princess_2" top="1">
      <attribute name="fullname" alias="princess_name_2" />
      <order attribute="createdon" descending="false" />
      <filter type="and">
        <condition attribute="princessid" operator="ne" value="{princess_1.princessid}" />
      </filter>
    </link-entity>
  </entity>
</fetch>

This returns a result set where each knight has up to two columns (princess_name_1 and princess_name_2) with their associated princesses.

Option 2: Fetch all related data, then pivot in your tool #

If your tool supports post-processing (like Power Apps collections, Power BI DAX, or Dataverse views), first fetch all knight-princess pairs with a basic FetchXML query, then use the tool’s features to group by knight and extract the first two princess names into separate columns.

For example, in Power BI, create two calculated columns with DAX:

Princess Name 1 = 
VAR KnightPrincesses = CALCULATETABLE(VALUES('Princess'[FullName]), ALLEXCEPT('Knight', 'Knight'[KnightID]))
RETURN INDEX(1, KnightPrincesses, ORDERBY('Princess'[CreatedOn], ASC))

Princess Name 2 = 
VAR KnightPrincesses = CALCULATETABLE(VALUES('Princess'[FullName]), ALLEXCEPT('Knight', 'Knight'[KnightID]))
RETURN INDEX(2, KnightPrincesses, ORDERBY('Princess'[CreatedOn], ASC))

Either approach will give you the two-column format you need, without relying on SQL.

内容的提问来源于stack exchange，提问作者pieeetr