CODEWITHSUNDEEP
sqlsql-server
1moreLearner
1moreLearner

Reputation: 81

SQL Server Sorting Multiple Values from different columns and reassign them based on ascending value

I have a select statement that brings the values of 5 different columns.

SELECT TOP 1 digit1, digit2, digit3, digit4, digit5 FROM myTable

The result is "25, 36, 1, 63, 12".

My question is, how do I sort these numbers in Ascending order and reassign them to their respectively Ascending columns? I.E: digit1 = 1, digit2 = 12, digit3 = 25, digit4 = 36, digit5 = 63.

Thanks in advance for the help.

Upvotes: 1

Views: 49

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

I would use apply:

select v.*
from (select top 1 digit1, digit2, digit3, digit4, digit5 FROM myTable
     ) t cross apply
     (select max(case when seqnum = 1 then digit end) as digit1,
             max(case when seqnum = 2 then digit end) as digit2,
             max(case when seqnum = 3 then digit end) as digit3,
             max(case when seqnum = 4 then digit end) as digit4,
             max(case when seqnum = 5 then digit end) as digit5
      from (select v.digit, row_number() over (order by v.digit) as seqnum
            from (values (digit1), (digit2), (digit3), (digit4), (digit5)
                 ) v(digit)
           ) v
     ) v;

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

You can do an UNPIVOT followed by a PIVOT

Example

Select *
 From (
        Select Item = concat('digit',Row_NUmber() over (Order by Value))
              ,Value
         From  (Select top 1 * from YourTable) A
         UnPivot ( Value for Digits in (digit1,digit2,digit3,digit4,digit5)  ) U
       ) A 
 Pivot (max(Value) for Item in (digit1,digit2,digit3,digit4,digit5) ) P

Returns

digit1  digit2  digit3  digit4  digit5
1       12      25      36      63

Upvotes: 1

Laughing Vergil
Laughing Vergil

Reputation: 3756

Using a CTE should allow this to be simplified to something like this. I am assuming that you need this for multiple rows, but simplifying it to a single row is easier than expanding a solution to multiple rows

WITH MyData as (
SELECT ID, digit1 as Digit FROM myTable
UNION ALL
SELECT ID, digit2 FROM myTable
UNION ALL
SELECT ID, digit3 FROM myTable
UNION ALL
SELECT ID, digit4 FROM myTable
UNION ALL
SELECT ID, digit5 FROM myTable
)
SELECT ID, Digit
FROM MyData
ORDER BY ID, Digit

Your data is now in order. A simple PIVOT will give you the output you are looking for.

Upvotes: 0

Jake H
Jake H

Reputation: 1740

If you want to order the results by multiple values, you can use the following syntax.

SELECT TOP 1 digit1, digit2, digit3, digit4, digit5 
FROM myTable
ORDER BY digit1, digit2, digit3, digit4, digit5

If the values need manipulation, you may need to create a UDF/CTE or inline expression to determine the 'value' of the columns to order them. Without a full working example (A SQLFiddle would be helpful), it is difficult to be sure.

Upvotes: 0

Related Questions