Rearranging values within a row

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

SQL Server 2005 Forums

Transact-SQL (2005)

Rearranging values within a row

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-23 : 14:29:59

Hello

I have asked this Q earlier and haven't been able to progress much from the information pointed to me.

i want to rearrange and update a table inside a record

create table #sort(
PR varchar(5),
val1 varchar (2),
val2 varchar (2),
val3 varchar (2),
)

insert into #sort(PR , val1, val2, val3)
select 2345X , ‘AU’, ‘ZZ’, ‘BK’ union all
select 2345Y , ‘AU’, ‘KX, ‘BK’ union all
select 2345Y , ‘AU’, ‘BZ, ‘BK’ union all
select 2345Y , ‘XY’, ‘ZZ’, ‘BK’ union all
select 2345A , ‘AB’, ‘AA’, ‘BK’ union all
select 2345A , ‘AU’, ‘ZZ’, ‘BK’ union all
select 2345A , ‘AU’, ‘ZZ’, ‘BK’ union all
select 2345B , ‘AU’, ‘ZZ’, ‘’ union all
select 2345B , ‘AU’, ‘’, ‘BK’ union all
select 2345B , ‘AU’, ‘ZZ’, ‘BK’ union all
select 2345C, ‘AU’, ‘’, ‘AA’ union all
select 2345C , ‘AU’, ‘ZZ’, ‘BK’ union all
select 2345C , ‘’, ‘ZZ’, ‘BK’ union all
select 2345D , ‘AU’, ‘ZZ’, ‘’

this is kind of unique so was wondering i anyone could help me

i want to sort the VAL variables horizontally in alphabetical order

if it is val1, val2 and val3 of ‘’, ‘ZZ’, ‘BK’, i need to sort to ‘BK’, ‘ZZ’, '' and update val1, val2 and val3 to ‘BK’, ‘ZZ’, ''

if it val1, val2 and val3 of 'AB', 'AA', 'AC', i need to sort to 'AA', 'AB', 'AC' and update val1, val2 and val3 to 'AA', 'AB', 'AC'

empty values ('') should always be in last variable - Val3
All help is appreciated.

ROBVOLK pointed me to an older post but i couldn't develop much from it

Thanks

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-05-23 : 16:22:04

please provide desired output, based on your DDL.

thank you

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-23 : 22:25:57

Hi,

This should work, assuming your table only has 3 value columns. If three value columns was simply used as an example and the real table could have more, then a more general solution would be needed.

The update statement after the main one is needed because sql considers blanks to be before, not after, other strings, and the query would get much messier trying to handle this in the main query.


update s 
set      val1 = (case when val1 < val2 and val1 < val3 then val1 
		when val2 < val3 and val2 < val1 then val2
		else val3 end) -- as 'MinValue',
	,val2 = (case when (val1 > val2 and val1 < val3) or (val1 < val2 and val1 > val3) then val1
		when (val2 > val1 and val2 < val3) or (val2 < val1 and val2 > val3) then val2
		else val3 end) -- as 'MiddleValue',
	,val3 = (case when val1 > val2 and val1 > val3 then val1 
		when val2 > val3 and val2 > val1 then val2
		else val3 end) -- as 'maxValue'
from #sort s

--Run 2 times if a record could have two blanks:
update #sort set val1 = val2, val2 = val3, val3 = val1 where isnull(val1, '') = ''

I hope this helps.

- Andrew Zwicker

Visit http://www.helpwithsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources