Sort data row in sql

Question

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

This STRONGLY indicates a database design issue. Your fields should mean something. Being able to rearrange them numerically means they all represent the same kind of data, which means they should probably be in a normalized table. — JNK
– JNK, Commented May 11, 2012 at 15:02

Gordon Linoff · Accepted Answer · 2012-05-11 14:55:59Z

1

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

edited May 11, 2012 at 14:55

answered May 11, 2012 at 14:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wildplasser Over a year ago

It is called normalisation. But you could view it as EAV-modelling, too.

Safaa Sharabati Over a year ago

database in sql 2008 .. i use asp.net2010 language .. the function not clear .. I Beginner

Romil Kumar Jain · Accepted Answer · 2012-05-11 14:24:31Z

0

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.

answered May 11, 2012 at 14:24

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

Comments

Purplegoldfish · Accepted Answer · 2012-05-11 14:27:21Z

0

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

answered May 11, 2012 at 14:27

Purplegoldfish

5,29410 gold badges41 silver badges59 bronze badges

1 Comment

Safaa Sharabati Over a year ago

ok i understand the example .. But it does not explain to me what I want .. (pivot) is ready function ?? or what ?

Collectives™ on Stack Overflow

Sort data row in sql

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related