SQL SERVER - Changing different records in a field based on another field

Asked

Viewed 35 times

0

Hello, I am trying to change a column in SQL SERVER (Query) based on another column. I have divergent social reason records for the same CNPJ, so I wish that for the same CNPJ only a social reason name is considered in the query, regardless of what the name.

Example of the Problem

Exemplo do Problema

Attempts Made

  • Subqueries
  • Joins com Distinct
    SELECT CODIGO_PARCEIRO, NOME_PARCEIRO
    FROM VW_DM_REPORTS
    WHERE CODIGO_PARCEIRO =  '27179922000164'

Solution

Solução desejada

Thanks in advance.

sql sql-server microsoft
  • 1

    Without the structure of your tables and sample data it is very difficult to opine in solving your problem

2 answers

2


SELECT CODIGO_PARCEIRO, MAX(NOME_PARCEIRO) AS NOME_PARCEIRO
  FROM VW_DM_REPORTS
 WHERE CODIGO_PARCEIRO =  '27179922000164'
 GROUP BY CODIGO_PARCEIRO

MAX

Returns the maximum value in the expression.

0

One possibility is to use the analytical function FIRST_VALUE() to returns the first value in an ordered set of values partitioned by the data of a specified column.

Data set for the test:

CREATE TABLE VW_DM_REPORTS(
  CODIGO_PARCEIRO VARCHAR(14), NOME_PARCEIRO VARCHAR(30)
);
  
INSERT INTO VW_DM_REPORTS VALUES (N'27179922000164', N'LUCENA TELECOM');
INSERT INTO VW_DM_REPORTS VALUES (N'27179922000164', N'LUCENA TELECOM');
INSERT INTO VW_DM_REPORTS VALUES (N'02570688000170', N'BRASIL TELECOM S/A');
INSERT INTO VW_DM_REPORTS VALUES (N'27179922000164', N'LUCENA TELECOM EIRELI');
INSERT INTO VW_DM_REPORTS VALUES (N'27179922000164', N'LUCENA TELECOM EIRELI');
INSERT INTO VW_DM_REPORTS VALUES (N'27179922000164', N'LUCENA TELECOM EIRELI');
INSERT INTO VW_DM_REPORTS VALUES (N'27179922000164', N'LUCENA TELECOM');
INSERT INTO VW_DM_REPORTS VALUES (N'02570688000170', N'BRASIL TELECOM');

Example 1:

SELECT CODIGO_PARCEIRO, 
       FIRST_VALUE(NOME_PARCEIRO) OVER (
         PARTITION BY CODIGO_PARCEIRO
         ORDER BY NOME_PARCEIRO ASC
       )
FROM VW_DM_REPORTS
WHERE CODIGO_PARCEIRO = '27179922000164'

Resultado do exemplo1

Example 2:

SELECT CODIGO_PARCEIRO, 
       FIRST_VALUE(NOME_PARCEIRO) OVER (
         PARTITION BY CODIGO_PARCEIRO
         ORDER BY NOME_PARCEIRO ASC
       )
FROM VW_DM_REPORTS

Resultado do exemplo2

Browser other questions tagged sql sql-server microsoft

You are not signed in. Login or sign up in order to post.