August 14, 2009 at 9:20 pm
hi guys, i have a table this way:
id keyword
1 banana
2 apple
3 cherry
4 orange
5 orange
6 apple
7 orange
8 melon
9 blackberry
10 orange
11 orange
12 orange
13 melon
14 melon
15 melon
16 melon
and i want this table to look this way:
id keyword
1 banana
2 apple
3 cherry
4 orange
6 apple
7 orange
8 melon
9 blackberry
10 orange
13 melon
how do i write the right sql statement for that?
thanks for any help.
August 14, 2009 at 10:20 pm
Started off making this harder than it was. here is the code I came up (also note how I set everything up, as this is how you should post your questions):
create table dbo.Word (
WordId int,
KeyWord varchar(16)
);
insert into dbo.Word
select 1, 'banana' union all
select 2, 'apple' union all
select 3, 'cherry' union all
select 4, 'orange' union all
select 5, 'orange' union all
select 6, 'apple' union all
select 7, 'orange' union all
select 8, 'melon' union all
select 9, 'blackberry' union all
select 10, 'orange' union all
select 11, 'orange' union all
select 12, 'orange' union all
select 13, 'melon' union all
select 14, 'melon' union all
select 15, 'melon' union all
select 16, 'melon';
select
w1.*
from
dbo.Word w1
left outer join dbo.Word w2
on w1.WordId = w2.WordId + 1
where
w1.KeyWord coalesce(w2.KeyWord,'');
drop table dbo.Word;
August 17, 2009 at 5:00 am
thanks very much lynn, i will try that.
have a nice day.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply