February 10, 2009 at 8:16 am
I have a table with numbers in 5 columns. Those numbers are not ordered from left to right. I need to be able to view it with the numbers ordered from left to right in ascending order.
I don't know how to do this. Can someone please show me how I can accomplish this?
Thanks!
[font="Courier New"]----------------------------------------------
------------ EXAMPLE TABLE ---------------
----------------------------------------------
ID VAL1 VAL2 VAL3 VAL4 VAL5
--- ---- ---- ---- ----- -----
1 21 7 16 44 9
2 14 33 5 8 21
3 48 15 2 10 37
4 16 37 45 20 12
5 5 11 22 28 8
6 9 41 6 48 12
7 36 30 29 8 1
8 15 23 45 18 42
9 2 3 5 18 39[/font]
February 10, 2009 at 8:22 am
could you show us the output for that example table please
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 8:30 am
[font="Courier New"]id val1 val2 val3 val4 val5
--- ---- ---- ---- ----- -----
1 21 7 16 44 9
2 14 33 5 8 21
3 48 15 2 10 37
4 16 37 45 20 12
5 5 11 22 28 8
6 9 41 6 48 12
7 36 30 29 8 1
8 15 23 45 18 42
9 2 3 5 18 39[/font]
February 10, 2009 at 8:30 am
You could use a series of Case statements.
select
case
when Val1 >= Val2 and Val1 >= Val3 and Val1 >= Val4 and Val1 >= Val5 then Val1
when Val2 >= Val1 and Val2 >= Val3 and Val2 >= Val4 and Val2 >= Val5 then Val2
when Val3 >= Val1 and Val3 >= Val2 and Val3 >= Val4 and Val3 >= Val5 then Val3
when Val4 >= Val1 and Val4 >= Val2 and Val4 >= Val3 and Val4 >= Val5 then Val4
when Val5 >= Val1 and Val5 >= Val2 and Val5 >= Val3 and Val5 >= Val4 then Val5
end as Val1
That'll get you the first column. The second one will be more complex, because you have to test that it's less than at least one value, or equal to the highest value, and greater than or equal to the rest. Third column, same, but two values higher.
The "All" function might help with this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 8:33 am
ok thing I found a solution. not sure how fast it will be but give it a go.
DECLARE @tbl TABLE
(ID INT IDENTITY(1,1),
VAL1 INT,VAL2 INT,VAL3 INT,VAL4 INT,VAL5 INT)
INSERT INTO @tbl
SELECT 21,7,16,44,9 UNION ALL
SELECT 14,33,5,8,21
SELECT * FROM @tbl
SELECT
id,
[1],
[2],
[3],
[4],
[5]
FROM
(
SELECT
id,
Number,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY Number) as ROWNUM
FROM
(SELECT ID, Val1, Val2, Val3, Val4, Val5
FROM @tbl) p
UNPIVOT
(Number FOR Val IN (Val1, Val2, Val3, Val4, Val5)
) as unpvt
) p
PIVOT
(MIN(Number)
FOR ROWNUM IN ([1],[2],[3],[4],[5])
) as pvt
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 8:33 am
I was thinking I could use Pivot but I couldn't figure out how to use it properly.
February 10, 2009 at 8:36 am
hahaha 😀 yeah... I couldn't figure out how to do that with Pivot but yes it works perfectly. I don't need speed... I just need a solution...
Thanks so much! I'm SO EXCITED NOW!!! :w00t:
February 10, 2009 at 8:37 am
Clever solution. I like it. Quite elegant. And the usual argument about the uselessness of the Pivot command is bypassed by having the columns be the Row_Number function results, which are highly predictable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 8:39 am
glad I could help 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 8:49 am
PIVOT is a unique solution. Thanks for that.
However, is there a reason this shouldn't be done on a client? Would seem that as this scales, you're using server resources for something you don't want to use them for.
February 10, 2009 at 9:37 am
it is in a ssis package that transforms data from one place to another and this is the format they wanted in the destination table. i just didn't know how to make the pivot work properly and this is a very workable solution.
February 10, 2009 at 9:39 am
if it's an SSIS I would check to see that perhaps the data doesn't start unpivoted and then is actually pivot for the results.
If this is the case then you could reorder the data before SSIS pivots it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 10, 2009 at 9:48 am
Great solution...using PIVOT.. i think, we can use this as the best example interms of using Pivot.
Thanks
Vijaya Kadiyala
February 10, 2009 at 9:50 am
Wow I didn't realise some folks where looking for a good pivot example 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply