--> 测试数据:#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([type] INT,[name] VARCHAR(1)) INSERT #tb SELECT 1,'a' UNION ALL SELECT 1,'b' UNION ALL SELECT 2,'c' UNION ALL SELECT 2,'d' --------------开始查询--------------------------SELECT [type],[name]=(SELECT ''+[name] FROM #tb WHERE [type]=t.[type] FOR XML PATH('')) FROM #tb AS t GROUP BY [type] ----------------结果---------------------------- /* type name 1 ab 2 cd */
select TYPE,(select name+'' from TB where TYPE=a.type for XML path('')) as name from TB as a group by type
/*适用于sql 2000 + 版本*/ -->测试数据 if object_id('tb')is not null drop table tb go create table tb(type int, name varchar(10)) insert into tb values(1, 'a') insert into tb values(1, 'b') insert into tb values(2, 'c') insert into tb values(2, 'd') go
create function dbo.f_str(@type int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + '' + name from tb where id=@type return @str end go
-- 调用函数 SELECt type, name = dbo.f_str(type) FROM tb GROUP BY type -->结果集 /* type name 1 ab 2 cd */ drop table tb drop function dbo.f_str
/*适用于sql 2000 + 版本*/ -->测试数据 if object_id('tb')is not null drop table tb go create table tb(type int, name varchar(10)) insert into tb values(1, 'a') insert into tb values(1, 'b') insert into tb values(2, 'c') insert into tb values(2, 'd') go
create function dbo.f_str(@type int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + '' + name from tb where type=@type return @str end go
-- 调用函数 SELECt type, name = dbo.f_str(type) FROM tb GROUP BY type -->结果集 /* type name 1 ab 2 cd */ drop table tb drop function dbo.f_str
如果最后还要再截取一下字符串呢?比如把每行name的第一个字符去掉,变成 type name 1 b 2 d 这应该怎么操作哦
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([type] INT,[name] VARCHAR(1))
INSERT #tb
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
--------------开始查询--------------------------SELECT [type],[name]=(SELECT ''+[name] FROM #tb WHERE [type]=t.[type] FOR XML PATH(''))
FROM #tb AS t
GROUP BY [type]
----------------结果----------------------------
/*
type name
1 ab
2 cd
*/
from TB as a
group by type
-->测试数据
if object_id('tb')is not null drop table tb
go
create table tb(type int, name varchar(10))
insert into tb values(1, 'a')
insert into tb values(1, 'b')
insert into tb values(2, 'c')
insert into tb values(2, 'd')
go
create function dbo.f_str(@type int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '' + name from tb where id=@type
return @str
end
go
-- 调用函数
SELECt type, name = dbo.f_str(type) FROM tb GROUP BY type -->结果集
/*
type name
1 ab
2 cd
*/
drop table tb
drop function dbo.f_str
-->测试数据
if object_id('tb')is not null drop table tb
go
create table tb(type int, name varchar(10))
insert into tb values(1, 'a')
insert into tb values(1, 'b')
insert into tb values(2, 'c')
insert into tb values(2, 'd')
go
create function dbo.f_str(@type int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '' + name from tb where type=@type
return @str
end
go
-- 调用函数
SELECt type, name = dbo.f_str(type) FROM tb GROUP BY type -->结果集
/*
type name
1 ab
2 cd
*/
drop table tb
drop function dbo.f_str
type name
1 b
2 d
这应该怎么操作哦