clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36246 in the last week).

select @@version;
(No column name)
Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)