求一SQL语句，解决如下问题，解决立刻给分
求一SQL语句，解决如下问题：
将  
  A                               B
PD_39_022         PD_30_401:PD_30_402:PD_30_403:PD_30_404

变为

          A                         B
      PD_39_022 PD_30_401
      PD_39_022 PD_30_402
      PD_39_022 PD_30_403
      PD_39_022 PD_30_404
请赐教！！谢谢。


------解决方案--------------------
declare @LongString varchar(50)
declare @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth int

set @LongString= 'AAAAA,BBBBB,CCCCC,ZZZZ,TTTT ' ----可以是某一个表的字段信息
set @lenth=len(@LongString)
create table #Temp_String(FID int identity,Content varchar(50))
set @FirstPoint=charindex( ', ',@LongString)

while( @FirstPoint> 0)
begin
set @FirstChar=substring(@LongString,0,@FirstPoint)
insert into #Temp_String(Content) values (@FirstChar)
set @LongString=substring(@LongString,@FirstPoint+1,@lenth)
set @FirstPoint=charindex( ', ',@LongString)
end

insert into #Temp_String(Content) values (@LongString)
select * from #Temp_String
drop table #Temp_String

结果显示：
FID Content
1 AAAAA
2 BBBBB
3 CCCCC
4 ZZZZ
5 TTTT
从而完成了我们的操作，当然你可以将其改为函数，需要注意的是在函数中不能使用临时表。
------解决方案--------------------

declare @col1 nvarchar(20),@col2 nvarchar(100),@str Nvarchar(100)
select @col1 = 'PD_39_022 ',@col2 = 'PD_30_401:PD_30_402:PD_30_403:PD_30_404 ',@str = ' '
declare @t table(col1 nvarchar(20),col2 nvarchar(100))while @col2 <> ' '
while @col2 <> ' '
begin
insert into @t select @col1,left(@col2,charindex( ': ',@col2+ ': ') - 1)
select @col2 = stuff(@col2,1,charindex( ': ',@col2+ ': '), ' ')
end;
select * from @t

col1 col2
PD_39_022 PD_30_401
PD_39_022 PD_30_402
PD_39_022 PD_30_403
PD_39_022 PD_30_404

------解决方案--------------------
if object_id( 'tempdb..#tmp ') is not null
drop table #tmp
GO
----生成临时表
select top 50 id = identity(int,1,1) into #tmp from syscolumns,sysobjects
----创建测试数据
declare @t table(A varchar(10),B varchar(100))
insert @t
select 'PD_39_022 ', 'PD_30_401:PD_30_402:PD_30_403:PD_30_404 '

----拆分
select x.A,substring(x.B,y.id,charindex( ': ',x.B + ': ',y.id) - y.id)
from @t as x inner join #tmp as y on substring( ': ' + x.B,y.id,1) = ': '

----清除测试环境
drop table #tmp

/*结果
A B
-------------------------------
PD_39_022 PD_30_401
PD_39_022 PD_30_402
PD_39_022 PD_30_403
PD_39_022 PD_30_404
*/
------解决方案--------------------
create table tb(A varchar(10),B varchar(50))
insert into tb values( 'PD_39_022 ', 'PD_30_401:PD_30_402:PD_30_403:PD_30_404 ')
go

SELECT TOP 8000
id = IDENTITY(int, 1, 1)
INTO #
FROM syscolumns a, syscolumns b

SELECT
A.a,
SUBSTRING(A.b, B.id, CHARINDEX( ': ', A.b + ': ', B.id) - B.id) b