Ufdjrw

I have a table like below,

col1 col2 col3
1 2 3
2 1 3
3 2 1
1 4 6
4 6 1
6 4 1

Here I want to sort the record row wise.

Expected output.

col1 col2 col3
1 2 3
1 2 3
1 2 3
1 4 6
1 4 6
1 4 6

I have used ASCII Values for comparison.

declare @tab table(col1 varchar(10),col2 varchar(10),col3 varchar(20))

insert into @tab
select '4','6','1' union
select '6','4','1' union
select '1','2','3' union
select '2','1','3' union
select '3','1','2' union
select '4','2','3' union
select '1','4','6' union

select '5','5','1' union
select '5','5','1' union

select 'a','2','2' union
select '2','a','2' union
select '2','2','a'



;with CTE as(
 Select Case When ascii(Col1) <= ascii(Col2) And ascii(Col1) <= 
 ascii(Col3) Then cast(Col1 as varchar)
 When ascii(Col2) <= ascii(Col1) And ascii(Col2) <= 
 ascii(Col3) Then cast(Col2 as varchar)
 Else cast(Col3 as varchar) END as col1,
 case when ( ascii(col1) >= ascii(col2) and ascii(col2) >= 
 ascii(col3)) or ( ascii(col3) >= ascii(col2) and 
 ascii(col2) >= ascii(col1)) then cast(Col2 as 
 varchar) 
 when ( ascii(col1) >= ascii(col3) and ascii(col3) >= 
 ascii(col2)) or ( ascii(col2) >= ascii(col3) and 
 ascii(col3) >= ascii(col1)) then cast(Col3 as varchar)
 when ( ascii(col3) >= ascii(col1) and ascii(col1) >= ascii(col2)) or ( ascii(col2) >= ascii(col1) and ascii(col1) >= ascii(col3)) then cast(Col1 as varchar) end as col2,
 Case When ascii(Col1) >= ascii(Col2) And ascii(Col1) >= ascii(Col3) Then cast(Col1 as varchar)
 When ascii(Col2) >= ascii(Col1) And ascii(Col2) >= ascii(Col3) Then cast(Col2 as varchar)
 Else cast(Col3 as varchar) END as col3
 From @tab)

 select * from CTE

Is there any shortest way to achieve this process?

Another way of doing it using ROW_NUMBER() like following

SELECT (SELECT x 
 FROM (SELECT x, 
 Row_number() 
 OVER( 
 ORDER BY x) rn 
 FROM (VALUES(col1), 
 (col2), 
 (col3))f(x))t 
 WHERE rn = 1) c1, 
 (SELECT x 
 FROM (SELECT x, 
 Row_number() 
 OVER( 
 ORDER BY x) rn 
 FROM (VALUES(col1), 
 (col2), 
 (col3))f(x))t 
 WHERE rn = 2) c2, 
 (SELECT x 
 FROM (SELECT x, 
 Row_number() 
 OVER( 
 ORDER BY x) rn 
 FROM (VALUES(col1), 
 (col2), 
 (col3))f(x))t 
 WHERE rn = 3) c3 
FROM @table 

OR using nested CTE like following.

 ;WITH cte1 
 AS (SELECT (SELECT Min(f) 
 FROM (VALUES (col1), 
 (col2), 
 (col3)) AS Fields(f)) m1, 
 * 
 FROM @table), 
 cte2 
 AS (SELECT (SELECT COALESCE(Min(f), M1) AS M2
 FROM (VALUES (col1), 
 (col2), 
 (col3)) AS Fields(f) 
 WHERE f > m1) m2, 
 * 
 FROM cte1), 
 cte3 
 AS (SELECT m1, 
 m2, 
 (SELECT COALESCE(Min(f),m2) as m3 
 FROM (VALUES (col1), 
 (col2), 
 (col3)) AS Fields(f) 
 WHERE f > m2) m3 
 FROM cte2) 
SELECT * 
FROM cte3 

Online Demo

This need for row-wise sorting is usually a sign that your tables could benefit from a new structure. What are you really trying to accomplish? It can probably be better done by normalizing col1, col2, and col3 to look more vertical (this is what the 'unpivoted' CTE is forcing below, but the table should look something like that in the first place).

If you must do this, consider adding a row identifier (basically a primary key) to your table.

declare @tab table(
 rowId int identity(1,1),
 col1 varchar(10),
 col2 varchar(10),
 col3 varchar(20)
);

insert @tab values
 ('4','6','1'),
 -- etc

Then you can avoid a bunch of case statements and more easily extend to more than just three columns with something like the following:

with

 unpivoted as (

 select rowId, 
 val,
 ord = row_number() over(partition by rowId order by val)
 from @tab
 cross apply (values (col1), (col2), (col3)) ap (val)

 )

 select rowId,
 col1 = [1],
 col2 = [2],
 col3 = [3]
 from unpvioted
 pivot (max(val) for ord in ([1],[2],[3])) piv

You can see it in action here.

use case when like below

select case when col1>col2>col3 then col1
 when col2>col3 then col2
 else col3 end as col1, -- this is the condition for first column

you have to write this way for 3columns

If all columns are Integer type then the below query will works,

SELECT c1 = CASE
 WHEN c1 <= c2 AND c1 <= c3 THEN c1
 WHEN c2 <= c1 AND c2 <= c3 THEN c2
 ELSE c3 END,
c2 = CASE 
 WHEN c1 <= c2 AND c1 <= c3 THEN 
 CASE WHEN c2 <= c3 THEN c2 ELSE c3 END
 WHEN c2 <= c1 AND c2 <= c3 THEN
 CASE WHEN c1 <= c3 THEN c1 ELSE c3 END
 ELSE
 CASE WHEN c1 <= c2 THEN c1 ELSE c2 END
 END,
c3 = CASE
 WHEN c1 >= c2 AND c1 >= c3 THEN c1
 WHEN c2 >= c1 AND c2 >= c3 THEN c2
 ELSE c3 END
FROM temp_x;

If any character then you can use ASCII value to cpompare.

The fastest way is likely to be case expressions, but that doesn't generalize.

I would go for apply as a good balance between performance and scalability:

select v.*
from t cross apply
 (select max(case when seqnum = 1 then col end) as col1,
 max(case when seqnum = 2 then col end) as col2,
 max(case when seqnum = 3 then col end) as col3 
 from (select v.*,
 row_number() over (order by col) as seqnum
 from (values (t.col1), (t.col2), (t.col3)) v(col)
 ) v
 ) v;

This can also readily handle NULL values and ties, which greatly complicate a case approach.