MSSQL_拆分

DECLARE @Tmp TABLE(id INT IDENTITY(1,1),TBookCode CHAR(6),TNodeID NUMERIC(18,0),TMaterialCode VARCHAR(50))
DECLARE @tmpa TABLE(id INT ,TTTypeID VARCHAR(30))
DECLARE @NewTmp TABLE(id INT IDENTITY(1,1),aid INT ,typeId VARCHAR(30))

INSERT INTO @Tmp (TBookCode,TNodeID,TMaterialCode) SELECT BookCode,NodeID,MaterialCode FROM dbo.MRelate WHERE CHARINDEX(',',TypeID)>0

INSERT INTO @tmpa SELECT t.id,r.TypeID FROM @Tmp t
LEFT JOIN dbo.MRelate r ON t.TBookCode=r.BookCode AND t.TMaterialCode=r.MaterialCode AND t.TNodeID=r.NodeID

WHILE EXISTS(SELECT TTTypeID FROM @tmpa WHERE CHARINDEX(',',TTTypeID)>0)
BEGIN
		
	INSERT INTO @NewTmp (aid,typeId)SELECT id,
		CASE WHEN CHARINDEX(',',TTTypeID)>0 THEN RTRIM(LTRIM(LEFT(TTTypeID,CHARINDEX(',',TTTypeID)-1))) 
			WHEN CHARINDEX(',',TTTypeID)=0 THEN RTRIM(LTRIM(TTTypeID)) END 
		FROM @tmpa
	
	DELETE @tmpa WHERE CHARINDEX(',',TTTypeID)=0
	
	UPDATE @tmpa SET TTTypeID=SUBSTRING(TTTypeID,CHARINDEX(',',TTTypeID)+1,30)
	
END

IF NOT EXISTS(SELECT TTTypeID FROM @tmpa WHERE CHARINDEX(',',TTTypeID)>0)
BEGIN
	INSERT INTO @NewTmp (aid,typeId)SELECT id,RTRIM(LTRIM(TTTypeID)) FROM @tmpa	
END

SELECT t.TBookCode,t.TNodeID,t.TMaterialCode,nt.typeId FROM @NewTmp nt

LEFT JOIN @Tmp t ON nt.aid = t.id ORDER BY nt.aid,nt.typeId

 

要出来的数据格式为

除了之后的数据格式为TTTypeID中的值只有一个

例如id=1的处理之后为两条记录，分别为

id   TTTypeID

1       1

1       11

 

要想达到这样的效果，应用MSSQL语句处理的方式如上。