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

变为

A B
PD_39_022PD_30_401
PD_39_022PD_30_402
PD_39_022PD_30_403
PD_39_022PD_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_022PD_30_401
PD_39_022PD_30_402
PD_39_022PD_30_403
PD_39_022PD_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_022PD_30_401
PD_39_022PD_30_402
PD_39_022PD_30_403
PD_39_022PD_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

FROM tb A, # B
WHERE SUBSTRING( ': ' + A.b, B.id, 1) = ': '

drop table tb,#

/*
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

（所影响的行数为 4 行）
*/
[解决办法]
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
A.A,
B = T.c.value( '.[1] ', 'varchar(10) ')
from(
select A, B = CONVERT(xml, ' <a> ' + REPLACE(B, ': ', ' </a> <a> ') + ' </a> ')
FROM tb
)A
OUTER APPLY A.B.nodes( '/a ')T(c)
go

drop table tb


-- 结果:
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

(4 行受影响)


[解决办法]
如果B列小于四部分可以学习老大的
create table #a(A varchar(10),B varchar(100))
insert #a select 'PD_39_022 ', 'PD_30_401:PD_30_402:PD_30_403:PD_30_404 '
----------------------
select A,ParseName(replace(B, ': ', '. '),1)B from #a
union all select A,ParseName(replace(B, ': ', '. '),2) from #a
union all select A,ParseName(replace(B, ': ', '. '),3) from #a
union all select A,ParseName(replace(B, ': ', '. '),4) from #a
order by A,B