Table columns in chronlogical order

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

Table columns in chronlogical order

Author

Topic

h2sut
Starting Member

40 Posts

Posted - 2010-05-12 : 20:32:20

I have table with values of 62,55,0,67. Is there away to arrange the data in table to be in 0,55,62,67. SO i need to put the values in order of smallest to largest. I have over 959 rows that i have to do this own.I cant figure it out?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 20:46:10

if 62,55,0,67 is string value you need first split the string then sort them at last concatenating rows

h2sut
Starting Member

40 Posts

Posted - 2010-05-12 : 20:56:45

Hi each value has it own column. so i have 4 columns with those values present

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 21:34:02

[code]declare @t table(recid int not null identity,col1 int, col2 int, col3 int , col4 int)

insert @t values(65,8,2,1)
insert @t values(1,3,2,5)
select * from @t
/*
recid col1 col2 col3 col4
----------- ----------- ----------- ----------- -----------
1 65 8 2 1
2 1 3 2 5
*/
;with cte as
(select recid, col1 from @t
union all
select recid, col2 from @t
union all
select recid, col3 from @t
union all
select recid, col4 from @t)

select recid,
col1=cast(parsename(col1, 4) as varchar(10)),
col2=cast(parsename(col1, 3) as varchar(10)),
col3=cast(parsename(col1, 2) as varchar(10)),
col4=cast(parsename(col1, 1) as varchar(10))
from
(
select recid,
cast(max(case when ranking=1 then col1 end) as varchar(20)) + '.' +
cast(max(case when ranking=2 then col1 end) as varchar(20)) + '.' +
cast(max(case when ranking=3 then col1 end) as varchar(20)) + '.' +
cast(max(case when ranking=4 then col1 end) as varchar(20)) as col1
from ( select recid, col1, ranking=(select count(*) from cte where recid=c.recid and col1<= c.col1)
from cte c
)d1
group by recid
)d

/*
recid col1 col2 col3 col4
----------- ---------- ---------- ---------- ----------
1 1 2 8 65
2 1 2 3 5
*/[/code]

UenTsin
Starting Member

4 Posts

Posted - 2010-05-12 : 23:29:16

Does 'Order by' work?

h2sut
Starting Member

40 Posts

Posted - 2010-05-12 : 23:34:05

I try what u had and that did not work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources