sql实现表内容拆分
ID nick content Forum theTime
1 030709200876 aaa#bbb#ccc## 1 2012-10-15 17:54:14.000
2 030709200876 AAA##BBB## 2 2012-10-16 14:56:32.000

如上图
我要拆分最后结果如下：
ID nick content Forum theTime
3 030709200876 aaa 1 2012-10-15 17:54:14.000
4 030709200876 bbb 1 2012-10-15 17:54:14.000
5 030709200876 ccc 1 2012-10-15 17:54:14.000

6 030709200876 AAA 2 2012-10-16 14:56:32.000
7 030709200876 BBB 2 2012-10-16 14:56:32.000

请问各位兄弟姐妹，怎么样处理成上述结果，谢谢先了

------解决方案--------------------
declare @t table (id int,nick varchar(20),content varchar(1000),Forum int ,theTime datetime)
insert into @t
select 1 as id, '030709200876' as nick, 'aaa#bbb#ccc##' as content,1 as Forum,'2012-10-15 17:54:14.000' as theTime union all
select 2,'030709200876','AAA##BBB##',2,'2012-10-16 14:56:32.000'

;with t as
(
select id
,nick
,content=replace(content,'##','#')
,Forum
,theTime
from @t
)
,t2 as
(
select nick,content=substring(content,1,charindex('#',content)-1)
,content_split=stuff(content,1,charindex('#',content),'')
,Forum
,theTime
from t
union all
select nick,content=substring(content_split,1,charindex('#',content_split)-1)
,content_split=stuff(content_split,1,charindex('#',content_split),'')
,Forum
,theTime
from t2
where content_split<>''
)
select row_number() over(order by forum,content) as id,nick,content,Forum,theTime
from t2

------解决方案--------------------

SQL code

create table hp
(ID int, nick varchar(14), content varchar(16), 
 Forum int, theTime datetime)

insert into hp
select 1, '030709200876', 'aaa#bbb#ccc##', 1, '2012-10-15 17:54:14.000' union all
select 2, '030709200876', 'AAA##BBB##', 2, '2012-10-16 14:56:32.000'


select a.ID,a.nick,
substring(replace(a.content,'##','#'),b.number,
charindex('#',replace(a.content,'##','#'),b.number)-b.number) 'content',
a.Forum,a.theTime
from hp a
inner join master.dbo.spt_values b
on b.number<=len(replace(a.content,'##','#')) and b.type='P'
where substring('#'+replace(a.content,'##','#'),b.number,1)='#'

/*
ID          nick           content    Forum       theTime
----------- -------------- ---------- ----------- -----------------------
1           030709200876   aaa        1           2012-10-15 17:54:14.000
1           030709200876   bbb        1           2012-10-15 17:54:14.000
1           030709200876   ccc        1           2012-10-15 17:54:14.000
2           030709200876   AAA        2           2012-10-16 14:56:32.000
2           030709200876   BBB        2           2012-10-16 14:56:32.000

(5 row(s) affected)
*/