SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (`id` INTEGER, `name1` TEXT, `name2` TEXT, `name3` TEXT);
✓
INSERT INTO tablename (`id`, `name1`, `name2`, `name3`) VALUES
  ('1', 'Hans', 'Peter', 'Frank'),
  ('2', 'Hans', 'Frank', 'Peter'),
  ('3', 'Hans', 'Peter', 'Frank'),
  ('4', 'Paul', 'Peter', 'Hans');
✓
WITH cte(id, name) AS (
  SELECT id, name1 FROM tablename
  UNION ALL
  SELECT id, name2 FROM tablename
  UNION ALL
  SELECT id, name3 FROM tablename
)
SELECT COUNT(*) count, name1, name2, name3
FROM (
  SELECT id,
         MAX(CASE WHEN rn = 1 THEN name END) name1,
         MAX(CASE WHEN rn = 2 THEN name END) name2,
         MAX(CASE WHEN rn = 3 THEN name END) name3
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) rn
    FROM cte
  )
  GROUP BY id
)
GROUP BY name1, name2, name3
HAVING COUNT(*) > 1;


count
name1
name2
name3


3
Frank
Hans
Peter


SELECT COUNT(*) count,
       MIN(name1, name2, name3) name_1,
       REPLACE(
         REPLACE(
           REPLACE(name1 || ',' || name2 || ',' || name3, MIN(name1, name2, name3), ''), 
           MAX(name1, name2, name3), ''), ',', ''
       ) name_2,
       MAX(name1, name2, name3) name_3
FROM tablename 
GROUP BY name_1, name_2, name_3 
HAVING COUNT(*) > 1;


count
name_1
name_2
name_3


3
Frank
Hans
Peter