SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE YourTable (
  "Name" VARCHAR(7),
  "Characteristic" VARCHAR(6),
  "Value" VARCHAR(6)
);

INSERT INTO YourTable
  ("Name", "Characteristic", "Value")
VALUES
  ('Mary', 'eyes', 'Blu'),
  ('Mary', 'hair', 'blonde'),
  ('Mary', 'Sex', 'Female'),
  ('Jhon', 'eyes', 'Black'),
  ('Jhon', 'Hair', 'Black'),
  ('Jhon', 'Sex', 'Male'),
  ('Jhon', 'Nation', 'Franch'),
  ('Bill', 'eyes', 'Blu'),
  ('Bill', 'Hair', 'Blond'),
  ('Bill', 'Sex', 'Male'),
  ('Will', 'eyes', 'Green'),
  ('Will', 'Hair', 'Blond'),
  ('Will', 'Sex', 'Male'),
  ('Will', 'Nation', 'Spain'),
  ('Lilly', 'eyes', 'Blu'),
  ('Lilly', 'Hair', 'Blonde'),
  ('Lilly', 'Sex', 'Female'),
  ('mark', 'eyes', 'Black'),
  ('mark', 'Hair', 'Black'),
  ('mark', 'Sex', 'Male'),
  ('mark', 'Nation', 'Franch'),
  ('Anna', 'eyes', 'Blu'),
  ('Anna', 'Hair', 'Blonde'),
  ('Anna', 'Sex', 'Female'),
  ('Antonio', 'eyes', 'Black'),
  ('Antonio', 'Hair', 'Black'),
  ('Antonio', 'Sex', 'Male'),
  ('Antonio', 'Nation', 'Franch');
28 rows affected

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;


GroupId
Characteristic
Value


2
eyes
Blu

2
Hair
Blond

2
Sex
Male

4
eyes
Green

4
Hair
Blond

4
Sex
Male

4
Nation
Spain

3
eyes
Blu

3
Hair
Blonde

3
Sex
Female

1
eyes
Black

1
Hair
Black

1
Sex
Male

1
Nation
Franch


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;


GroupId
Name
Characteristic
Value


3
Mary
eyes
Blu

3
Mary
hair
blonde

3
Mary
Sex
Female

1
Jhon
eyes
Black

1
Jhon
Hair
Black

1
Jhon
Sex
Male

1
Jhon
Nation
Franch

2
Bill
eyes
Blu

2
Bill
Hair
Blond

2
Bill
Sex
Male

4
Will
eyes
Green

4
Will
Hair
Blond

4
Will
Sex
Male

4
Will
Nation
Spain

3
Lilly
eyes
Blu

3
Lilly
Hair
Blonde

3
Lilly
Sex
Female

1
mark
eyes
Black

1
mark
Hair
Black

1
mark
Sex
Male

1
mark
Nation
Franch

3
Anna
eyes
Blu

3
Anna
Hair
Blonde

3
Anna
Sex
Female

1
Antonio
eyes
Black

1
Antonio
Hair
Black

1
Antonio
Sex
Male

1
Antonio
Nation
Franch

GroupId	Characteristic	Value
2	eyes	Blu
2	Hair	Blond
2	Sex	Male
4	eyes	Green
4	Hair	Blond
4	Sex	Male
4	Nation	Spain
3	eyes	Blu
3	Hair	Blonde
3	Sex	Female
1	eyes	Black
1	Hair	Black
1	Sex	Male
1	Nation	Franch

GroupId	Name	Characteristic	Value
3	Mary	eyes	Blu
3	Mary	hair	blonde
3	Mary	Sex	Female
1	Jhon	eyes	Black
1	Jhon	Hair	Black
1	Jhon	Sex	Male
1	Jhon	Nation	Franch
2	Bill	eyes	Blu
2	Bill	Hair	Blond
2	Bill	Sex	Male
4	Will	eyes	Green
4	Will	Hair	Blond
4	Will	Sex	Male
4	Will	Nation	Spain
3	Lilly	eyes	Blu
3	Lilly	Hair	Blonde
3	Lilly	Sex	Female
1	mark	eyes	Black
1	mark	Hair	Black
1	mark	Sex	Male
1	mark	Nation	Franch
3	Anna	eyes	Blu
3	Anna	Hair	Blonde
3	Anna	Sex	Female
1	Antonio	eyes	Black
1	Antonio	Hair	Black
1	Antonio	Sex	Male
1	Antonio	Nation	Franch