一行转多行,一条记录转多条记录(sql server 2000)

最新推荐文章于 2024-10-25 14:00:10 发布

原创最新推荐文章于 2024-10-25 14:00:10 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #insert #table #go #c

    create       table       tb(id       int,mytype       varchar(400))
    insert       into       tb
    select       1, 'a,b,c,d'       union       all
    select       2,           'c,f,a'       union       all
    select       3,           'g'

    declare       @id       int,@mytype       varchar(10)
    declare       @tb       table       (id       int,mytype       varchar(10))
    declare       cur       cursor       for
    select       id,mytype       from       tb
    open       cur
    fetch       next       from       cur       into       @id,@mytype
    while       @@fetch_status=0
      begin
        set       @mytype=@mytype+ ','
        while       charindex( ',',@mytype)> 0
        begin
              insert       into       @tb      values(@id,left(@mytype,charindex( ',',@mytype)-1)       )
              set       @mytype=right(@mytype,len(@mytype)-charindex( ',',@mytype))
        end
       fetch       next       from       cur       into       @id,@mytype

      end
select * from @tb
go