求一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
请赐教!!谢谢。
将
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 @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth intset @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)
endinsert 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 @tcol1 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
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
*/
insert into tb values('PD_39_022', 'PD_30_401:PD_30_402:PD_30_403:PD_30_404')
goSELECT TOP 8000
id = IDENTITY(int, 1, 1)
INTO #
FROM syscolumns a, syscolumns bSELECT
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 行)
*/
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)
godrop 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 #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