Incremental ordering of columns, but per result line

0

I have the following table structure and results:

Ineedtocreateanefficientstatementthatcansortvaluesinascendingorder,buttheyshouldbesortedbycolumn/rowandnotjustbycolumn.

Intheexampleabove,theexpectedresultwouldbe:

Thanks in advance for your help. Thank you.

    
asked by anonymous 25.07.2017 / 16:15

1 answer

0

I found an efficient way to get the columns sorted according to the values in the rows.

From now on I thank Motta for suggesting the study of "unpivot / pivot".

Below I leave the table creation code according to the data presented in the question:

create table TabelaTeste
(
    Col1 int,
    Col2 int,
    Col3 int,
    Col4 int
)

insert into TabelaTeste
select 18, 20, 25, 23
union
select 23, 15, 5, 4
union
select 20, 23, 12, 8
union
select 16, 5, 25, 24

And the resolution for the increasing ordering of values according to column / line.

--Recupera o número da linha
--Pode ser utilizado para conferência dos valores
; with numerado as
(
   select *,
         row_number() over (order by (select null)) NumeroLinha
   from TabelaTeste
),
-- Cria a ordenação desejada
-- dentro de um "unpivoted data"
ordenado as
(
  select *, row_number() over (partition by NumeroLinha order by v asc) nl from numerado 
 -- Informe as colunas desejadas aqui
 unpivot (v for c in (Col1, Col2, Col3, Col4)) u
)
-- Retorna os dados no novo layout
select NumeroLinha,
       [1] Col1,
       [2] Col2,
       [3] Col3,
       [4] Col4
  from 
  (    
    select NumeroLinha,
           v,
           Nl
      from ordenado
  ) o
 pivot (min(v) for Nl in ([1], [2], [3], [4])) p
    
25.07.2017 / 20:06