动态添加列

if exists (select * from sysobjects where name='aa')
drop proc aa
go
create proc aa
as
    create table #tb2
     (
     id int,
     uName varchar(10)
     );
    declare @id int,@uName varchar(10),@tName varchar(10)
    declare cur_se cursor for select u.id,u.name,t.Name from [User] u inner join dbo.UserTb t on u.id=t.Pid;
    open cur_se
    declare @i int;
    set @i=0;
    declare @c varchar(10)
    declare @v varchar(10)
    declare @count int
    fetch next from cur_se into @id,@uName,@tName
    while(@@FETCH_STATUS=0)
        begin 
        select @count=COUNT(0) from #tb2 where id=@id;
        if(@count=0)
            begin
            insert into #tb2(id,uName) values(@id,@uName) 
            end
        set @i=@i+1
        set @c=CONVERT(varchar(10),@i)
        exec('alter table #tb2 add uName'+@c+' varchar(20)')
        set @v=@tName
        exec('update #tb2 set uName'+@c+'='''+@v+''' where id='+@id+'')
        fetch next from cur_se into @id,@uName,@tName
        end
     close cur_se
     deallocate cur_se
     select * from #tb2
go
exec aa

表结构：

USE [Text]
GO
/****** 对象:  Table [dbo].[User]    脚本日期: 01/09/2014 22:19:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

USE [Text]
GO
/****** 对象:  Table [dbo].[UserTb]    脚本日期: 01/09/2014 22:19:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserTb](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Pid] [int] NULL,
    [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_UserTb] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

动态添加列

推荐阅读