Distribute values in column, SQL

Question

I am 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 column 'unit', to which I assign the value '1' to have equally distributed, 4 values - say, by way of example the values: 1,41,51,61.

Thus, if my query above returned 100 lines, the "unit" column would have the first 25 lines with the value '1', the subsequent 25 lines the value '41', then 51 and so on.

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

Answer 1

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 to a block. The column was used as an example nu-cpf as an ordination 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 table @Values should be informed the numbers that will be part of the column ìdentidade, in the order in which they are to be distributed.