根据分号‘;’将一行数据拆分成多行。直接贴代码
例如:code o_pegging 两列
105533@000000 NG1592119N5N862954-1/平磨/32
111198@000000 NKC20181018N3N888990-10/平磨/28;NKC20181018N3N888990-1/平磨/8
修改后:
105533@000000 NG1592119N5N862954-1/平磨/32
111198@000000 NKC20181018N3N888990-10/平磨/28
111198@000000 NKC20181018N3N888990-1/平磨/8
delete from ERP_APS.dbo.tmp_sprit
SELECT A.code, N'' + T.c.value('.', 'nvarchar(max)') + N'' as o_pegging
into #tmp
FROM ERP_APS.dbo.EX_ORDER A
CROSS APPLY(SELECT CONVERT(xml,
'<r><![CDATA['
+ REPLACE(SUBSTRING(o_pegging, 1, LEN(o_pegging)), N';', N']]></r><r><![CDATA[')
+ ']]></r>'
)) B(c)
CROSS APPLY B.c.nodes('/r/text()') T(c)
insert ERP_APS.dbo.tmp_sprit(code,o_pegging,process,qty)
select code,parsename(replace(o_pegging,'/','.'),3) ord
,parsename(replace(o_pegging,'/','.'),2) pro
,parsename(replace(o_pegging,'/','.'),1) qty
from #tmp
select * from ERP_APS.dbo.tmp_sprit