Distribute column values, SQL

Question

Distribute column values, SQL

Navigation

#1 by (3 votes)

0

I'm extracting data from a table with the following query:

SELECT
[nu-cpf] as CPF, [nome segurado] as NOME, '' as IdentificacaoCliente, 
'' as CodigoBeneficio, 
CONCAT([nu-ddd],[nu-telefone]) TelefoneResidencial,
'' as TelefoneCelular,'' as TelefoneAlternativo,cast([dt-nasc] as date) as
DataNascimento, '1' as unidade

FROM
mac_all

And I need the 'unit' column, which I assign the value '1' to have equally distributed, 4 values - say, for example, the values: 1,41,51,61.

So, if my above query returns 100 rows, the "unit" column would have the first 25 rows with the value '1', the subsequent rows the value '41', then 51, and so on. p>

If it were only 2 values, I could use the "top 50 percent" clause, but since there are 4, I do not know the ways to make it work. Is there any way to do it in the SQL code itself? Or do I need to extract the data and do this manually via excel, how have I done?

sql sql-server

asked by anonymous 10.08.2018 / 20:09

1 answer

SQL Updating Wrong Value Decimal field displays a .0 on the screen

score 3 · Accepted Answer

One option is to use the sort function NTILE to divide the result of the query into blocks, numbering them sequentially, and then associating each value with a block. As an example, the nu-cpf column was used as the sort criterion.

-- código #1 v2
-- informe os valores a distribuir no resultado da consulta
declare @Valores table (Ordem int identity, Valor int);
INSERT into @Valores (Valor) values (1), (41), (51), (61);

-- calcula a quantidade de valores a distribuir
declare @QtdV int;
set @QtdV = (SELECT count(*) from @Valores);

--
with Consulta as (
SELECT [nu-cpf] as CPF, [nome segurado] as NOME, 
       '' as IdentificacaoCliente, '' as CodigoBeneficio, 
       CONCAT([nu-ddd],[nu-telefone]) as TelefoneResidencial,
       '' as TelefoneCelular,'' as TelefoneAlternativo,
       cast([dt-nasc] as date) as DataNascimento, 
       ntile(@QtdV) over (order by [nu-cpf]) as NBloco
  from mac_all 
)
SELECT CPF, NOME, IdentificacaoCliente, CodigoBeneficio,
       TelefoneResidencial, TelefoneCelular, TelefoneAlternativo,
       DataNascimento, V.Valor as unidade
  from Consulta as C
       inner join @Valores as V on V.Ordem = C.NBloco;

In the @Values table, you must enter the numbers that will be part of the ìdentidade column, in the order in which they are to be distributed.