由于同事需要这个功能,花了几分钟写了下,有需要的朋友可以看看。
Code
/*
打点编码(树)转换成指针方式(树)
*/
declare @Code nvarchar(256), @ParentCode nvarchar(256),@Id uniqueidentifier,@ParentId uniqueidentifier
DECLARE MyCursor CURSOR FOR
select code from Tree order by Code
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @Code
WHILE @@FETCH_STATUS = 0
BEGIN
if(Charindex('.',@Code,0) = 0)
begin
--直接插入
insert into treetest values (newid(),@Code,null)
end
else
begin
--找到父级Id,赋值再插入
select @ParentCode = @Code
select @ParentCode = Reverse(@ParentCode)
select @ParentCode = substring(@ParentCode,charindex('.',@ParentCode) + 1,len(@ParentCode) - charindex('.',@ParentCode))
select @ParentCode = Reverse(@ParentCode)
select @ParentId = Id from treetest where Code = @ParentCode
insert into treetest values (newid(),@Code,@ParentId)
end
FETCH NEXT FROM MyCursor
INTO @Code
END
CLOSE MyCursor
DEALLOCATE MyCursor