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语句处理的方式如上。