Order numbers in a row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order numbers in a row

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2010-07-23 : 16:47:06

Hi all.
I have table like:
Item Cost1 Cost2 Cost3 Cost4 Cost5
Apple 2 1 4 5 3

I need to sort costs from lowest to highest to get

Item Cost1 Cost2 Cost3 Cost4 Cost5
Apple 1 2 3 4 5

thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-23 : 17:10:49

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146143

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-23 : 17:54:14

[code]
/* Solution1 */
SELECT T.Item,
MAX(CASE WHEN T.row_num = 1 THEN T.cost END) AS Cost1,
MAX(CASE WHEN T.row_num = 2 THEN T.cost END) AS Cost2,
MAX(CASE WHEN T.row_num = 3 THEN T.cost END) AS Cost3,
MAX(CASE WHEN T.row_num = 4 THEN T.cost END) AS Cost4,
MAX(CASE WHEN T.row_num = 5 THEN T.cost END) AS Cost5
FROM (SELECT T.Item, T.cost, ROW_NUMBER() OVER(PARTITION BY T.Item ORDER BY T.cost) AS row_num
FROM (SELECT I.Item,
CASE T.n
WHEN 1 THEN I.Cost1
WHEN 2 THEN I.Cost2
WHEN 3 THEN I.Cost3
WHEN 4 THEN I.Cost4
ELSE I.Cost5
END AS cost
FROM Items AS I
CROSS JOIN
(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS T(n)) AS T) AS T
GROUP BY T.Item;

/* Solution2 */
SELECT I.Item,
MAX(CASE WHEN C.row_num = 1 THEN C.cost END) AS Cost1,
MAX(CASE WHEN C.row_num = 2 THEN C.cost END) AS Cost2,
MAX(CASE WHEN C.row_num = 3 THEN C.cost END) AS Cost3,
MAX(CASE WHEN C.row_num = 4 THEN C.cost END) AS Cost4,
MAX(CASE WHEN C.row_num = 5 THEN C.cost END) AS Cost5
FROM Items AS I
CROSS APPLY
(SELECT cost, ROW_NUMBER() OVER(ORDER BY cost) AS row_num
FROM (VALUES(Cost1),(Cost2),(Cost3),(Cost4),(Cost5)) AS C(cost)) AS C
GROUP BY I.Item;
[/code]

Sachin.Nand

2937 Posts

Posted - 2010-07-24 : 09:42:17


select 
 max (case when rid=1 then col end)as cost1,
 max (case when rid=2 then col end)as cost2,
 max (case when rid=3 then col end)as cost3,
 max (case when rid=4 then col end)as cost4,
 max (case when rid=5 then col end)as cost5
 from
(
select  *,row_number()over(partition by item order by col)as rid from
(
select * from yourtable)u
unpivot
(col for columns in (cost1,cost2,cost3,cost4,cost5))v

)t

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-07-26 : 09:23:38

Solution 4
Normalize your data

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources