Resort Columns Low-to-High

Question

Resort Columns Low-to-High

texpic

SSCertifiable

Points: 5887
More actions
August 19, 2008 at 11:12 am

#154637

I have a table with data in 4 columns.
C1 C2 C3 C4
110 99 230 2
I'd like to add 4 more columns and populate it with the data sorted from low-to-high. I'm probably not saying this right, I'm thinking the example is probably better.
S1 S2 S3 S4
2 99 101 230
Thanks for any suggestions!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Megistal SSCrazy Eights Points: 8787 More actions · Answer 1

The first thing I get in mind (and I know it's far from the best solution out there 4 sort operations per line returned) would be using a UDF.

SELECT

CASE dbo.getPosition (Col1, Col2, Col3, Col4, pos1) as Low

CASE dbo.getPosition (Col1, Col2, Col3, Col4, pos2) as MediumLow

CASE dbo.getPosition (Col1, Col2, Col3, Col4, pos3) as MediumHigh

CASE dbo.getPosition (Col1, Col2, Col3, Col4, pos4) as High

FROM TABLE

getPosition(arg1, arg2, arg3, arg4, index)

sort arg1,2,3,4

find value at index

return value

texpic SSCertifiable Points: 5887 More actions · Answer 2

Thanks for your response. I'm out of town so not near my SQL box to try this. Wouldn't I need the UDF also? Thanks.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

swtrans (8/19/2008)
I have a table with data in 4 columns.
C1 C2 C3 C4
110 99 230 2
I'd like to add 4 more columns and populate it with the data sorted from low-to-high. I'm probably not saying this right, I'm thinking the example is probably better.
S1 S2 S3 S4
2 99 101 230
Thanks for any suggestions!

Not for anything am I trying to be a smart guy about this and I know it's probably due to someone else's, ummmm, poor design habits... but this is a terrible non-normalized design for a table. My first recommendation is that you don't need a UDF... you need to recommend that they fix the design of the table so the code doesn't suffer scalability and performance problems in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)