求多行合并的sql语句(200)

数据表如下no 部门 人员1 电脑部 陈刚2 电脑部 王强3 人事部 张三4 人事部 李四 5 人事部 王五要求把以上数据表合并存入table2中 部门 人员 电脑部 陈刚，王强 人事部 张三，李四，王五

竖变横，你百度一下，牛毛一样。

需要遍历数据表，应该要用游标。

DECLARE @Name VARCHAR(10)DECLARE @Sql1 VARCHAR(8000)DECLARE @Sql VARCHAR(8000)DECLARE @TypeName VARCHAR(10)SET @Sql1 = 'CREATE TABLE table2(部门 varchar(10),人员 varchar(200))'EXEC(@Sql1)SET @Sql1 = 'INSERT INTO table2 (部门,人员) VALUES ('''DECLARE curType CURSOR READ_ONLY FORWARD_ONLY FOR (SELECT Distinct 部门 FROM 表)OPEN curTypeFETCH NEXT FROM curType INTO @TypeNameWHILE @@Fetch_Status = 0BEGINSET @Sql=''DECLARE curType1 CURSOR READ_ONLY FORWARD_ONLY FOR (SELECT Distinct 人员 FROM 表 WHERE 部门=@TypeName)OPEN curType1FETCH NEXT FROM curType1 INTO @NameWHILE @@Fetch_Status = 0BEGINIF @Sql=''BEGINSET @Sql = @NameENDELSEBEGINSET @Sql = @Sql +',' + @NameENDFETCH NEXT FROM curType1 INTO @NameENDCLOSE curType1DEALLOCATE curType1SET @Sql=@Sql1+@TypeName+''','''+@Sql+''')'EXEC(@Sql)PRINT @SqlFETCH NEXT FROM curType INTO @TypeNameENDCLOSE curTypeDEALLOCATE curType

感觉上面的对数据库的操作过于多，修改如下：DECLARE @Name VARCHAR(10)DECLARE @TypeName1 VARCHAR(10)DECLARE @Sql1 VARCHAR(8000)DECLARE @Sql VARCHAR(8000)DECLARE @TypeName VARCHAR(10)SET @Sql1 = 'CREATE TABLE table2(部门 varchar(10),人员 varchar(200))'EXEC(@Sql1)SET @Sql1 = 'INSERT INTO table2 (部门,人员) VALUES ('''DECLARE curType CURSOR READ_ONLY FORWARD_ONLY FOR SELECT 部门,人员 FROM 表 ORDER BY 部门OPEN curTypeFETCH NEXT FROM curType INTO @TypeName,@NameSET @TypeName1=@TypeNameSET @Sql=''WHILE @@Fetch_Status = 0BEGIN--PRINT @TypeName--PRINT @NameIF @TypeName1=@TypeNameBEGINIF @Sql=''BEGINSET @Sql = @NameENDELSEBEGINSET @Sql = @Sql +',' + @NameENDENDELSEBEGINSET @Sql=@Sql1+@TypeName1+''','''+@Sql+''')'EXEC(@Sql)--PRINT @SqlSET @Sql=''SET @Sql = @NameENDSET @TypeName1=@TypeNameFETCH NEXT FROM curType INTO @TypeName,@NameENDSET @Sql=@Sql1+@TypeName1+''','''+@Sql+''')'EXEC(@Sql)--PRINT @SqlCLOSE curTypeDEALLOCATE curType

declare @tableA table(部门 varchar(30))declare @tableB table(部门 varchar(30),人员 varchar(30))insert into @tableA select 部门 from 【表】 group by 部门declare @Department nvarchar(30),@Str nvarchar(2000)declare myCursor cursor for select 部门 from @tableAopen myCursorfetch next from myCursor into @Departmentwhile @@FETCH_STATUS=0 begin set @Str='' select @str=@str+','+人员 from 【表】 where 部门=@Department set @str=rtrim(substring(@str,2,1000)) insert into @tableB values(@Department,@str) fetch next from myCursor into @Departmentendclose myCursordeallocate myCursorselect * from @tableBgo