您可以使用STRING_AGG将所有特征连接在一起，然后使用ROW_NUMBER和DENSE_RANK计算它们。然后你将它重新加入到基表中。

对于您的第一个查询，您可以这样做。

SELECT
  Groups.GroupId,
  t.Characteristic,
  t.Value
FROM YourTable t
JOIN (
    SELECT
      t.Name,
      t.GroupDefinition,
      GroupId = DENSE_RANK() OVER (ORDER BY t.GroupDefinition),
      RowId = ROW_NUMBER() OVER (PARTITION BY t.GroupDefinition ORDER BY t.Name)
    FROM (
        SELECT
          t.Name,
          GroupDefinition = STRING_AGG(Characteristic + ':' + Value, '|')
              WITHIN GROUP (ORDER BY t.Characteristic)
        FROM YourTable t
        GROUP BY
          t.Name
    ) t
) Groups ON Groups.Name = t.Name
WHERE Groups.RowId = 1;

第二个查询如下。

SELECT
  Groups.GroupId,
  t.*
FROM YourTable t
JOIN (
    SELECT
      t.Name,
      t.GroupDefinition,
      GroupId = DENSE_RANK() OVER (ORDER BY t.GroupDefinition),
      RowId = ROW_NUMBER() OVER (PARTITION BY t.GroupDefinition ORDER BY t.Name)
    FROM (
        SELECT
          t.Name,
          GroupDefinition = STRING_AGG(Characteristic + ':' + Value, '|')
              WITHIN GROUP (ORDER BY t.Characteristic)
        FROM YourTable t
        GROUP BY
          t.Name
    ) t
) Groups ON Groups.Name = t.Name;

db<>小提琴

另一种选择是将其聚合为 JSON 或 XML 格式，然后在不重新加入基表的情况下将其切碎。