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.
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 62 73 84 95 NULL |
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-28 : 02:41:34
|
try thisdeclare @t table(id int)insert into @t select 1insert into @t select 2insert into @t select 3insert into @t select 4insert into @t select 5insert into @t select 6insert into @t select 7insert into @t select 8insert into @t select 9declare @cnt intselect @cnt = count(*) from @tselect t.id,t1.idfrom @t tleft join @t t1 on t.id =t1.id - case when (@cnt%2)<>0 then @cnt/2+1 else @cnt /2 endwhere t.id <= case when (@cnt%2)<>0 then @cnt/2+1 else @cnt /2 end |
|
|
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 thisset nocount ondeclare @t table(item int, id int)insert into @t select 1, 1insert into @t select 1, 2insert into @t select 1, 3insert into @t select 1, 4insert into @t select 1, 5insert into @t select 1, 6insert into @t select 1, 7insert into @t select 1, 8insert into @t select 1, 9insert into @t select 2, 1insert into @t select 2, 2insert into @t select 2, 3insert into @t select 2, 4insert into @t select 2, 5insert into @t select 2, 6insert into @t select 2, 7and i got the result with this:select item, count(*) cntinto #tfrom @twhere id between @from and @togroup by itemselect t.item, t.id,t1.idfrom @t tinner join #t t2 on t.item = t2.itemleft 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 endwhere t.id <= case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 endorder by t.item, t.iddrop table #tbut sometimes the users want id to be in a specific range for example ids between 2 to 7. i changed the qurey againselect @from = 2, @to = 7select item, count(*) cntinto #tfrom @twhere id between @from and @togroup by itemselect t.item, t.id,t1.idfrom @t tinner join #t t2 on t.item = t2.itemleft 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 endwhere t.id <= case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 endorder by t.item, t.idgodrop table #tbut it doesnt return the correct result. how can i solve it? |
|
|
sridhar.dbe
Starting Member
34 Posts |
Posted - 2009-03-02 : 04:44:09
|
is this what you want?declare @from int,@to intselect @from = 2, @to = 7declare @cnt intselect item, count(*) cntinto #t12from @tgroup by itemselect t.item, t.id,t1.idfrom @t tinner join #t12 t2on t.item = t2.itemleft 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 endwhere t.id <= case when (t2.cnt%2)<>0 then t2.cnt/2+1 else t2.cnt /2 endand t.id between @from and @toorder by t.item, t.idOUTPUTitem id id1 2 71 3 81 4 91 5 NULL2 2 62 3 72 4 NULLisk |
|
|
|
|
|
|
|