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 2000 Forums
 SQL Server Development (2000)
 showing a column in two

Author  Topic 

akhenpato
Starting Member

2 Posts

Posted - 2009-02-28 : 00:21:41
suppose we have a table with a column that contains number from 1 to 9 (the real data is more than that). How can I get the below result?

col1 col2
---- ----
1 6
2 7
3 8
4 9
5 NULL

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-28 : 02:41:34
try this
declare @t table(id int)
insert into @t select 1
insert into @t select 2
insert into @t select 3
insert into @t select 4
insert into @t select 5
insert into @t select 6
insert into @t select 7
insert into @t select 8
insert into @t select 9
declare @cnt int
select @cnt = count(*) from @t
select t.id,t1.id
from @t t
left join @t t1 on t.id =t1.id - case when (@cnt%2)<>0 then @cnt/2+1 else @cnt /2 end
where t.id <= case when (@cnt%2)<>0 then @cnt/2+1 else @cnt /2 end
Go to Top of Page

akhenpato
Starting Member

2 Posts

Posted - 2009-03-02 : 00:54:35
In my table i have another column (for example item) and the real data is some thing like this

set nocount on
declare @t table(item int, id int)
insert into @t select 1, 1
insert into @t select 1, 2
insert into @t select 1, 3
insert into @t select 1, 4
insert into @t select 1, 5
insert into @t select 1, 6
insert into @t select 1, 7
insert into @t select 1, 8
insert into @t select 1, 9
insert into @t select 2, 1
insert into @t select 2, 2
insert into @t select 2, 3
insert into @t select 2, 4
insert into @t select 2, 5
insert into @t select 2, 6
insert into @t select 2, 7

and i got the result with this:

select item, count(*) cnt
into #t
from @t
where id between @from and @to
group by item

select t.item, t.id,t1.id
from @t t
inner join #t t2
on t.item = t2.item
left join @t t1 on t.item = t1.item and t.id =t1.id - case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 end
where t.id <= case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 end
order by t.item, t.id

drop table #t

but sometimes the users want id to be in a specific range for example ids between 2 to 7. i changed the qurey again

select @from = 2, @to = 7

select item, count(*) cnt
into #t
from @t
where id between @from and @to
group by item

select t.item, t.id,t1.id
from @t t
inner join #t t2
on t.item = t2.item
left join @t t1 on t.item = t1.item and t.id =t1.id - case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 end
where t.id <= case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 end
order by t.item, t.id
go

drop table #t

but it doesnt return the correct result. how can i solve it?
Go to Top of Page

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-03-02 : 04:44:09
is this what you want?

declare @from int,@to int
select @from = 2, @to = 7

declare @cnt int
select item, count(*) cnt
into #t12
from @t
group by item

select t.item, t.id,t1.id
from @t t
inner join #t12 t2
on t.item = t2.item
left join @t t1 on t.item = t1.item and t.id =t1.id - case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 end
where t.id <= case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 end
and t.id between @from and @to
order by t.item, t.id

OUTPUT
item id id
1 2 7
1 3 8
1 4 9
1 5 NULL

2 2 6
2 3 7
2 4 NULL

isk
Go to Top of Page
   

- Advertisement -