一个SQL

declare @a varchar(100)
set @a = 'A：我们不需要你的服务 B:人不行 C:正在考虑 D:硬件不达标 E:还没有辞职 F:明天上班 G:还不确定';
select
  substring(@a,charindex('A:',@a)+2,(charindex('B:',@a)-charindex('A:',@a)-2)) A
, substring(@a,charindex('B:',@a)+2,(charindex('C:',@a)-charindex('B:',@a)-2)) B
, substring(@a,charindex('C:',@a)+2,(charindex('D:',@a)-charindex('C:',@a)-2)) C
, substring(@a,charindex('D:',@a)+2,(charindex('E:',@a)-charindex('D:',@a)-2)) D
, substring(@a,charindex('E:',@a)+2,(charindex('F:',@a)-charindex('E:',@a)-2)) E
, substring(@a,charindex('F:',@a)+2,(charindex('G:',@a)-charindex('F:',@a)-2)) F
, substring(@a,charindex('G:',@a)+2,(len(@a)-charindex('G:',@a)-2))

 

 

 SQL解决:  下面是一个表(#table9),只有一列(deptname),有4个不同的名字,怎么把相同的名字提取出来放在4个新表里?

 

    deptname

 

   aaaaaaaaa

   aaaaaaaaa

   aaaaaaaaa

   aaaaaaaaa

   aaaaaaaaa

   aaaaaaaaa

   aaaaaaaaa

   aaaaaaaaa

   bbbbbbbbb

   bbbbbbbbb

   bbbbbbbbb

   bbbbbbbbb

   bbbbbbbbb

   bbbbbbbbb

   bbbbbbbbb

   bbbbbbbbb

   cccccccccccc

   cccccccccccc

   cccccccccccc

   cccccccccccc

   cccccccccccc

   cccccccccccc

   ddddddddd

   ddddddddd

   ddddddddd

   ddddddddd

   ddddddddd

   ddddddddd

   ddddddddd

 

 

     select IDENTITY(INT,1,1) as seq, DeptName into #table10  from #table9 group by DeptName

     declare @deptname varchar(20),@num int,@num2 int

     select @deptname=deptname from #table10 where seq = 1

     while exists(select * from #table10)
      begin
       
       select * from #table9 table12 where DeptName =@deptname for xml auto
       select @num = min(seq) from #table10
       delete from #table10 where seq = @num

       select @num = min(seq) from #table10
       select @deptname = deptname from #table10 where seq = @num
      end
       

    
     drop table #table9,#table10