将sql表中的数据多行动态的转为多列

先把贴图贴上

 

这是表中的数据

 

（1）这是第一种效果，不知道能不能满足你的需求

 

（2）如果以上效果不满足，那你可能需要的是第二种了

 

好了，效果看过了，代码贴上

（1）这是第一种效果的代码

SELECT Name,
(SELECT Interest+'--' FROM Table_4   WHERE Name=A.Name   FOR XML PATH('')) AS List FROM Table_4 A
GROUP BY Name

 

（2）这是第二中效果的代码

declare @c int,@i int
declare @sql nvarchar(800)
declare @cur cursor
declare @Name nvarchar(50)
declare @Interest nvarchar(50)

 set @i=1
 set @c=( select top(1) C from (select COUNT(1) as C from Table_4 group by Name) as tb1 order by C desc)
 

--动态创建一张临时表
 set @sql = 'create table tmp(Name nvarchar(50) '
 while(@i<=@c)
 begin
  set @sql = @sql + ' ,Interest'+CAST(@i as varchar(2))+' nvarchar(50)'
  set @i = @i+1
 end
 set @sql =@sql+ ' ,num int )'
--print(@sql)
 exec(@sql)
 

--通过游标动态将多行转为多列
set @cur = cursor  for select Name,Interest from table_4
open @cur
fetch next from @cur into @Name,@Interest
while(@@FETCH_STATUS=0)
begin
 set @c = (select COUNT(1) from tmp where Name=@Name)
 if @c=0 --insert value
  begin
   insert into tmp(Name,Interest1,num) values(@Name,@Interest,1)
  end
 else
  begin
   set @i = (select num from tmp where Name=@Name)+1
   set @sql='update tmp set Interest'+cast(@i as varchar(10))+' = ^$^'+@Interest+'^$^ , num='+cast(@i as varchar(10))+' where Name=^$^'+@Name+'^$^'
   set @sql = (select replace(@sql,'^$^',''''))
   exec(@sql)
  end
 fetch next from @cur into @Name,@Interest
end
select * from tmp

 

 

实现的代码已经提供，下面是建表的脚本

--创建表
CREATE TABLE [dbo].[Table_4](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Interest] [nvarchar](50) NOT NULL,
 [State] [int] NULL,
 CONSTRAINT [PK_Table_4] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]