CODEWITHSUNDEEP
sqlsorting
Safaa Sharabati
Safaa Sharabati

Reputation: 1

Sort data row in sql

please help me i have columns from more than one table and the data type for all these columns is integer

i want to sort the data row (not columns (not order by)) Except the primary key column

for example 

column1(pk)   column2         column3         column4          column5
1             6               5               3                1
2             10              2               3                1
3             1               2               4                3

How do I get this result

column1(pk)   column2         column3         column4          column5
1             1               3               5                6
2             1               2               3                10
3             1               2               3                4

Please help me quickly .. Is it possible ?? or impossible ??? if impossible how I could have a similar result regardless of sort

Upvotes: 0

Views: 2663

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

What database are you using? The capabilities of the database are important. Second, this suggests a data structure issue. Things that need to be sorted would normally be separate entities . . . that is, separate rows in a table. The rest of this post answers the question.

If the database supports pivot/unpivot you can do the following:

(1) Unpivot the data to get in the format , , (2) Use row_number() to assign a new column, based on the ordering of the values. (3) Use the row_number() to create a varchar new column name. (4) Pivot the data again using the new column.

You can do something similar if this functionality is not available.

First, change the data to rows:

(select id, 'col1', col1 as val from t) union all
(select id, 'col2', col2 from t) union all
. . .

Call this byrow. The following query appends a row number:

select br.*, row_number() over (partition by id order by val) as seqnum
from byrow br

Put this into a subquery to unpivot. The final solution looks like:

with byrow as (<the big union all query>)
select id,
       max(case when seqnum = 1 then val end) as col1,
       max(case when seqnum = 2 then val end) as col2,
       ...
from (select br.*, row_number() over (partition by id order by val) as seqnum
      from byrow br
     ) br
group by id

Upvotes: 1

Purplegoldfish
Purplegoldfish

Reputation: 5284

Here is a good example using PIVOT, you should be able to adapt this to meet your needs

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

Upvotes: 0

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

You can use pivot function of sql server to convert the row in column. Apply the sorting there and again convert column to row using unpivot.

Upvotes: 0

Related Questions