sql节点转换

 

create table #temp3
(
node char(50),
name varchar(10)
)
declare  cursor1  cursor  for  
select node,name from T_1

declare  @node  nchar(10);
declare  @name  nchar(10);
declare @node_after varchar(30);
declare @node_bef char(4);
declare @dot char(1);
declare @num int;
declare @i int;

open cursor1;
fetch next from cursor1 into @node,@name;

while @@FETCH_STATUS = 0
begin      
     set @node_after = '';
     set @dot = '-'    
     set @num = CAST(SUBSTRING(@node,3,3) as int);
     set @node_bef = SUBSTRING(@node,1,2);     
     set @i = 1
     
     while @i <= @num
       begin
         if @i = @num
           begin
              set @dot = ''
           end
         set @node_after = @node_after + @node_bef + CAST(@i AS CHAR(1)) + @dot;
         set @i = @i + 1;    
       end
     insert into #temp3(node,name) values (@node_after,@name);  
     fetch next from cursor1  into @node, @name;
end

close cursor1;
deallocate cursor1;

SELECT * FROM #temp3
DROP TABLE #temp3;