テスト

WITH T0 as (
WITH T0 as (
SELECT 
    "貝桝" as name,
    "A" as TEL1,
    "B" as TEL2,
    "C" as TEL3

UNION ALL 

SELECT 
    "貝桝" as name,
    "B" as TEL1,
    null as TEL2,
    null as TEL3

-- UNION ALL 

-- SELECT 
--     "貝桝" as name,
--     "B" as TEL1,
--     "L" as TEL2,
--     "N"  as TEL3

-- UNION ALL 

-- SELECT 
--     "貝桝" as name,
--     "D" as TEL1,
--     "K" as TEL2,
--     null  as TEL3

-- UNION ALL 

-- SELECT 
--     "貝桝" as name,
--     "A" as TEL1,
--     "D" as TEL2,
--     "F" as TEL3

-- UNION ALL 

-- SELECT 
--     "貝桝" as name,
--     "K" as TEL1,
--     null as TEL2,
--     null as TEL3

-- UNION ALL 

-- SELECT 
--     "貝桝" as name,
--     "G" as TEL1,
--     null as TEL2,
--     null as TEL3
),
T1 as (
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY name order by tel1,tel2,tel3) as KK
FROM T0
),

T2 as (
SELECT 
    T1.*,    
    T01.name as T01_name,
    T01.TEL1 as T01_TEL1,
    T01.TEL2 as T01_TEL2,
    T01.TEL3 as T01_TEL3,
    T01.KK as T01_KK,
    
FROM T1,T1 as T01
where T1.name = T01.name and T1.KK != T01.KK
      and (T01.TEL1 IN (T1.TEL1,T1.TEL2,T1.TEL3) )
)

,FN AS (
SELECT 
    name,
    TEL1,
    STRING_AGG(CONCAT(IFNULL(TEL2,"@"),",",IFNULL(TEL3,"@"),",",IFNULL(T01_TEL1,"@"),",",IFNULL(T01_TEL2,"@"),",",IFNULL(T01_TEL3,"@"))) as GG,
    IFNULL(LENGTH(REPLACE(STRING_AGG(CONCAT(IFNULL(TEL2,"@"),",",IFNULL(TEL3,"@"),",",IFNULL(T01_TEL1,"@"),",",IFNULL(T01_TEL2,"@"),",",IFNULL(T01_TEL3,"@"))),"@","")),0) as FF_LN,
    ROW_NUMBER() OVER (PARTITION BY name order by TEL1, STRING_AGG(CONCAT(IFNULL(TEL2,"@"),",",IFNULL(TEL3,"@"),",",IFNULL(T01_TEL1,"@"),",",IFNULL(T01_TEL2,"@"),",",IFNULL(T01_TEL3,"@"))) ) as K
FROM T2    
GROUP BY 1,2)

,without as (--除外
SELECT 
    FN.name,
    FN1.K
FROM FN
LEFT JOIN FN as FN1
ON FN.name = FN1.name 
    AND FN.K != FN1.K
    AND FN.FF_LN > FN1.FF_LN
WHERE FN1.K is not null
)

,FN2 as (
SELECT 
    FN.name,
    SPLIT(CONCAT(TEL1,",",GG),",") as GG1,
    ROW_NUMBER() OVER (PARTITION BY FN.name order by CONCAT(TEL1,",",GG)) as K
FROM FN 
LEFT JOIN without 
ON FN.name = without.name
and FN.K = without.K
WHERE without.name is null)

,FN3 as (
SELECT 
    DISTINCT
    name,
    v,
    K
FROM FN2
, UNNEST(GG1) AS v)

SELECT * FROM FN3 WHERE v != "@"