SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE GRUPO_CLIENTES
    (
         CODGRUPO    INT
        ,CPF_CNPJ    VARCHAR(15)
    )

    CREATE TABLE CLIENTES
    (
         CODCLIENTE        INT PRIMARY KEY IDENTITY
        ,NOME            VARCHAR(50)
        ,CPF_CNPJ        VARCHAR(15)
    )

    INSERT INTO CLIENTES
    VALUES
         ('AURORA', '37306895000105')
        ,('SADIA','37306895000222') 
        ,('PERDIGAO','37306895000333')
        ,('SEARA','37306895000444')
        ,('EMIRATES', '55172540000144')
        ,('AZUL','55172540000222')
        ,('TAM S/A','55172540000333')
        ,('GOL LINHAS AEREAS S/A','55172540000444')
        ,('FISHER PRICE','62085953000157')
        ,('RI HAPPY','62085953000222')
        ,('ESTRELA','62085953000333')
        ,('MATEL','62085953000444')
        ,('DAYCOVAL','06877315000106')
        ,('ITAU','06877315000222')
        ,('SANTANDER','06877315000333')
        ,('BRADESCO','06877315000444')
        ,('HABIBS','09429621000160')
        ,('MCDONALDS','09429621000111')
        ,('BOBS','09429621000222')
        ,('BURGER KING','09429621000333')
        ,('BMW','82779204000137')
        ,('FORD','82779204000222')
        ,('FIAT','82779204000333')
        ,('CHEVROLET','82779204000444')


INSERT INTO GRUPO_CLIENTES VALUES
            (10, '37306895000105')
        ,(10,'37306895000222') 
        ,(10,'37306895000333')
        ,(10,'37306895000444')
        ,(10,'37306895000555')
        ,(15, '55172540000144')
        ,(15,'55172540000222')
        ,(15,'55172540000333')
        ,(15,'55172540000444')
        ,(15,'30933404000555')
        ,(20,'62085953000157')
        ,(20,'62085953000222')
        ,(20,'62085953000333')
        ,(20,'62085953000444')
        ,(20,'62085953000555')
        ,(25,'06877315000106')
        ,(25,'06877315000222')
        ,(25,'06877315000333')
        ,(25,'06877315000444')
        ,(25,'06877315000555')
        ,(30,'09429621000160')
        ,(30,'09429621000111')
        ,(30,'09429621000222')
        ,(30,'09429621000333')
        ,(35,'82779204000137')
        ,(35,'82779204000222')
        ,(35,'82779204000333')
        ,(35,'82779204000444')
        ,(35,'82779204000555')


    CREATE TABLE MAPA_RISCO_VINCULOS
    (
         ID_VINCULO        INT PRIMARY KEY IDENTITY
        ,CNPJ_VINCULO    VARCHAR(15)
        ,CNPJ_ASSOCIADO VARCHAR(15)
        ,ATIVO            BIT
    )

    INSERT INTO MAPA_RISCO_VINCULOS VALUES
     ( '37306895000105', '55172540000333', 1) -- AURORA+ TAM            10 + 15
    ,( '62085953000444', '37306895000444', 1) -- MATEL + SEARA        20 + 10    
    ,( '82779204000333', '62085953000222', 1) -- FIAT + RI HAPPY        35 + 20
    ,('06877315000444', '09429621000160' , 1) -- BRADESCO + HABIBS    25 + 30 
57 rows affected

DECLARE @vinculoCom VARCHAR(15)
DECLARE @codgrupo INT

SET @vinculoCom = '82779204000137'
SET @codgrupo = (SELECT codgrupo 
                 FROM grupo_clientes 
                 WHERE cpf_cnpj = @vinculoCom)
BEGIN
    WITH ctevinculos (cpf_cnpj)
    AS (
      -- Initialization excerpt below.
       SELECT cpf_cnpj
       FROM grupo_clientes
       WHERE codgrupo = @codgrupo
  
    UNION ALL

      -- Recursive excerpt below.
       SELECT gc2.cpf_cnpj
       FROM mapa_risco_vinculos mrv
       INNER JOIN ctevinculos v ON mrv.cnpj_vinculo = v.cpf_cnpj
       INNER JOIN grupo_clientes gc1 ON mrv.cnpj_associado = gc1.cpf_cnpj
       INNER JOIN grupo_clientes gc2 ON gc1.codgrupo = gc2.codgrupo
    )
    SELECT c.codcliente, c.nome, c.cpf_cnpj
    FROM clientes c
    JOIN ctevinculos v ON c.cpf_cnpj = v.cpf_cnpj
END


codcliente
nome
cpf_cnpj


1
AURORA
37306895000105

2
SADIA
37306895000222

3
PERDIGAO
37306895000333

4
SEARA
37306895000444

5
EMIRATES
55172540000144

6
AZUL
55172540000222

7
TAM S/A
55172540000333

8
GOL LINHAS AEREAS S/A
55172540000444

9
FISHER PRICE
62085953000157

10
RI HAPPY
62085953000222

11
ESTRELA
62085953000333

12
MATEL
62085953000444

21
BMW
82779204000137

22
FORD
82779204000222

23
FIAT
82779204000333

24
CHEVROLET
82779204000444

codcliente	nome	cpf_cnpj
1	AURORA	37306895000105
2	SADIA	37306895000222
3	PERDIGAO	37306895000333
4	SEARA	37306895000444
5	EMIRATES	55172540000144
6	AZUL	55172540000222
7	TAM S/A	55172540000333
8	GOL LINHAS AEREAS S/A	55172540000444
9	FISHER PRICE	62085953000157
10	RI HAPPY	62085953000222
11	ESTRELA	62085953000333
12	MATEL	62085953000444
21	BMW	82779204000137
22	FORD	82779204000222
23	FIAT	82779204000333
24	CHEVROLET	82779204000444