0
I have the following table structure and results:
I need to create an efficient instruction able to sort the values in ascending form, but they should be sorted by column/row and not only by column.
In the case of the above example, the expected result would be:
Thanks for your help. Thank you.
0
I found an efficient way to get the columns sorted according to the values shown in the rows.
Thanks to Motta for suggesting the study of "unpivot/pivot".
Below I leave the code of the creation of the table 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 increasing order of values according to column/row.
--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
I like it when the person who came in with the doubt "thinks" the solution, this I will even save because I may need one day ...
Thank you so much for the tip Motta. Hugs.
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
Show the table structure, an initial hint would be to assemble an "unpivot" of the columns into rows , sort them and then sort this output. https://technet.microsoft.com/pt-br/library/ms177410(v=sql.105). aspx
– Motta
You have to read the line sort the values line by line, do by part, and use a loop to navigate between each line.
– Marco Souza
An "unpivot" can also be done by Union select col1 from table Union select col2 from table ...
– Motta
I did some instructions via loop, but it was not so practical. I believe there is a better way to do this. I will search for "unpivot". Thank you gentlemen.
– TonCunha