declare @tab table ( id int, name nvarchar(50), type nvarchar(50) )insert into @tab(id,name,type) select 1,'北京','综合' union all select 1,'北京','专业' union all select 2,'天津','专业' union all select 2,'天津','综合'select id,name,type=(select type+',' from @tab where id=t.id and name=t.name for xml path('')) from @tab t group by id,name
create table t(id int ,name varchar(50),type varchar(100)) insert into t select 1,'北京','综合' union all select 1,'北京','专业' union all select 2,'天津','专业' union all select 2,'天津','综合' union allselect distinct id,name,type=(select ','+type from t b where a.id=b.id and a.name=b.name for xml path('')) from t a
您好,老师提示 'xml' 附近有语法错误,该怎么办?我用的sql 2000
您好,老师您给的解决方法也是提示 'xml' 附近有语法错误,该怎么办?我用的sql 2000
if object_id('F_Str') is not null drop function F_Str go create function F_Str(@id int) returns nvarchar(100) as begin declare @S nvarchar(100) select @S=isnull(@S+',','')+type from Tb where id=@id return @S end go Select distinct id,name,type=dbo.F_Str(Col1) from Tb go
谢谢版主,列名 'Col1' 无效怎么办?
if object_id('F_Str') is not null drop function F_Str go create function F_Str(@id int) returns nvarchar(100) as begin declare @S nvarchar(100) select @S=isnull(@S+',','')+type from Tb where id=@id return @S end go Select distinct id,name,type=dbo.F_Str(type) from Tb
go改成TYPE
谢谢版主,列名 'Col1' 无效怎么办?Col1 就是要传的列名。 Select distinct id,name,type=dbo.F_Str(id) from Tb
版主,提示将 nvarchar 值 '综合' 转换为数据类型为 int 的列时发生语法错误。
版主,提示将 nvarchar 值 '综合' 转换为数据类型为 int 的列时发生语法错误。Select distinct id,name,type=dbo.F_Str(id) from Tb
谢谢版主,列名 'Col1' 无效怎么办?Col1 就是要传的列名。 Select distinct id,name,type=dbo.F_Str(id) from Tb 正解,运行成功了,谢谢老师提示。
(
id int,
name nvarchar(50),
type nvarchar(50)
)insert into @tab(id,name,type)
select 1,'北京','综合'
union all
select 1,'北京','专业'
union all
select 2,'天津','专业'
union all
select 2,'天津','综合'select id,name,type=(select type+',' from @tab where id=t.id and name=t.name for xml path('')) from @tab t group by id,name
insert into t
select 1,'北京','综合' union all
select 1,'北京','专业' union all
select 2,'天津','专业' union all
select 2,'天津','综合' union allselect distinct id,name,type=(select ','+type from t b where a.id=b.id and a.name=b.name for xml path('')) from t a
drop function F_Str
go
create function F_Str(@id int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+type from Tb where id=@id
return @S
end
go
Select distinct id,name,type=dbo.F_Str(Col1) from Tb go
drop function F_Str
go
create function F_Str(@id int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+type from Tb where id=@id
return @S
end
go
Select distinct id,name,type=dbo.F_Str(type) from Tb
go改成TYPE
Select distinct id,name,type=dbo.F_Str(id) from Tb
Select distinct id,name,type=dbo.F_Str(id) from Tb
正解,运行成功了,谢谢老师提示。