Help with query

Hi,

Iam having problem writing a particular query.

The Table looks like this:

1 a#b#c

2 d#e

3 d

4 a#b

and the ouput returned by the query should be

1 a

1 b

1 c

2 d

2 e

3 d

4 a

4 b

Thanks in advance for any suggestions/help.

Regards,

PP

Maybe something like this?

declare @.theTable table
( rid integer,
rString varchar (30)
)
insert into @.theTable values (1, 'a#b#c')
insert into @.theTable values (2, 'd#e')
insert into @.theTable values (3, 'd')
insert into @.theTable values (4, 'a#b');

with theCTE
as
(
select rid,
1 as level,
cast(case when charindex ('#', rString) = 0 then rstring
else left (rString, charindex ('#', rString)-1)
end as varchar(30))
as rString,
cast(case when charindex ('#', rstring) = 0 then ''
else substring (rString, charindex ('#', rString)+1, 30)
end as varchar(30))
as nextString
from @.theTable
union all
select rid,
level + 1 as level,
cast(case when charindex ('#', nextString) = 0 then nextString
else left (nextString, charindex ('#', nextString)-1)
end as varchar(30))
as rString,
cast(case when charindex ('#', nextString) = 0 then ''
else substring (nextString, charindex ('#', nextString)+1, 30)
end as varchar(30))
as nextString
from theCTE
where nextString <> ''
)
select rid,
rString
from theCTE
order by rid, rString

-- -- Sample Output: --

-- rid rString
-- --
-- 1 a
-- 1 b
-- 1 c
-- 2 d
-- 2 e
-- 3 d
-- 4 a
-- 4 b

|||Thank you very much.Works like a charm.