In sql i want get distict sets of rows : identical group for Characteristic and Value only one time :

The column Characteristic can range from one to 10

Table :

The result that i want to achieve :

and finally if it's possible :

CodePudding user response：

You can use STRING_AGG to join all the characteristics together, then use ROW_NUMBER and DENSE_RANK to count them. Then you re-join that back to the base table.

For your first query, you can do it like this.

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;

The second query is as follows.

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<>fiddle

Another option would be to aggregate it into a JSON or XML format, then shred it back out without re-joining the base table.