Picking out largest string value from 8 columns

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)

Picking out largest string value from 8 columns

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-09 : 05:16:17

Okay I have 8 Columns all with postcodes (basically did a case function to pull out all different combinations)

I know which one has the largest string value is going to be the correct value as some patterns overlap.

What I need is some sort of case statement which looks at these 8 values and picks out largest string value, and puts it into its own column, so I ahve a single column.

Column Names etc are irrelevent as I can change them so for example we can call is Column1, column2 etc if necessary.

Thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-09 : 07:05:17

Please define "largest" and what it means to you.

1) The longest (most characters)?
2) The last when sorted alphabetically?

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 07:38:31

FYI with that kind of table design you are violating the basic form of normalisation i.e the 1NF.I would highly recommend to you to change it if you can.

Anyways below will give you a basic idea on how to get your desired o/p.


declare @tbl as table(id int identity(1,1),col1 int,col2 int,col3 int)
insert into @tbl
select 1,2, 5 union all
select 4,1, 3

select * from @tbl

select id,MAX(col)MaxValue from
(
	select *  from
	(
	select * from @tbl
	)u
		unpivot (col for NullColumns in(col1,col2,col3))v 
)T group by id

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

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources