创建存储过程sppbConvertStringToTable,实现数据的拆分,使以下代码得以正常运行,运行结果如下图
SELECT uGuid='A',sOrderNO='AAA',sWorkerNo='001,002,003' INTO #TEMP INSERT INTO #TEMP VALUES('B','BBB','002,004') INSERT INTO #TEMP VALUES('C','CCC','') --测试空值
SELECT sGroup=uGuid,sNo=sWorkerNo,iCount=Convert(integer,0) INTO #StringTable FROM #TEMP
EXEC dbo.sppbConvertStringToTable ',',1
SELECT * FROM #TEMP ORDER BY uGuid
SELECT A.uGuid,A.sOrderNo ,B.sNo,B.iCount FROM #TEMP A INNER JOIN #StringTable B ON B.sGroup=A.uGuid ORDER BY B.sGroup
DROP TABLE #StringTable
DROP TABLE #TEMP