先把贴图贴上
这是表中的数据
(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]