create table tb(Goods varchar(10),Price decimal(8,2)) insert into tb select 'AAA',0.1 insert into tb select 'BBB',0.1 insert into tb select 'CCC',0.2 insert into tb select 'DDD',0.3 insert into tb select 'FFF',0.1 insert into tb select 'GGG',0.12 insert into tb select 'HHH',0.2 insert into tb select 'JJJ',0.3 go select stuff((select ' '+Goods from tb where Price=a.Price for xml path('')),1,1,'')Goods,Price from tb a group by Price /* Goods Price ---------------------------- --------------------------------------- AAA BBB FFF 0.10 GGG 0.12 CCC HHH 0.20 DDD JJJ 0.30(4 行受影响)*/ go drop table tb
sql server 2000?还是2005?2008?
楼上的大哥,忘记告诉你,我用的是ms sql 2000了。
--合并列值 --原著:邹建 --改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳--表结构,数据如下: /* id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc */ --需要得到结果: /* id values ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即:group by id, 求value 的和(字符串相加) */ --1. 旧的解决方法(在sql server 2000中只能用函数解决。) --1. 创建处理函数 create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go create function dbo.f_str(@id int) returns varchar(8000) as begin declare @r varchar(8000) set @r = '' select @r = @r + ',' + value from tb where id=@id return stuff(@r, 1, 1, '') end go -- 调用函数 SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id drop table tb drop function dbo.f_str /* id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc (所影响的行数为2 行) */ --2、另外一种函数. create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go --创建一个合并的函数 create function f_hb(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(value as varchar) from tb where id = @id set @str = right(@str , len(@str) - 1) return(@str) End go --调用自定义函数得到结果: select distinct id ,dbo.f_hb(id) as value from tb drop table tb drop function dbo.f_hb /* id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc (所影响的行数为2 行) */ SQL SERVER 2000的话需要用自定义函数,2005+的话用晴天大大的就可以了。
新年快乐!这么快就人有回复了啊 我用的是 sql 2000
表a name num aa 1 bb 2 cc 4 dd 4 ee 2 一条语句实现如下: name aa,bb,cc,dd,ee DECLARE @STR VARCHAR(8000) SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T SELECT @STR 问题描述: 无论是在sql 2000,还是在sql 2005 中,都没有提供字符串的聚合函数, 所以,当我们在处理下列要求时,会比较麻烦: 有表tb, 如下: id value 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果: id values 1 aa,bb 2 aaa,bbb,ccc 即,group by id, 求value 的和(字符串相加) 1. 旧的解决方法 -- 1. 创建处理函数 CREATE FUNCTION dbo.f_str(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + value FROM tb WHERE id=@id RETURN STUFF(@r, 1, 1, '') END GO -- 调用函数 SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id -- 2. 新的解决方法 -- 示例数据 DECLARE @t TABLE(id int, value varchar(10)) INSERT @t SELECT 1, 'aa' UNION ALL SELECT 1, 'bb' UNION ALL SELECT 2, 'aaa' UNION ALL SELECT 2, 'bbb' UNION ALL SELECT 2, 'ccc' -- 查询处理 SELECT *FROM (SELECT DISTINCT Id FROM @t)A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM @t N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, '') )N /*--结果 id values ----------- ---------------- 1 aa,bb 2 aaa,bbb,ccc (2 行受影响) --*/ --各种字符串分函数 --3.3.1 使用游标法进行字符串合并处理的示例。 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 --合并处理 --定义结果集表变量 DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) --定义游标并进行合并处理 DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDER BY col1,col2 DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100) OPEN tb FETCH tb INTO @col1,@col2 SELECT @col1_old=@col1,@s='' WHILE @@FETCH_STATUS=0 BEGIN IF @col1=@col1_old SELECT @s=@s+','+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1 END FETCH tb INTO @col1,@col2 END INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) CLOSE tb DEALLOCATE tb --显示结果并删除测试数据 SELECT * FROM @t DROP TABLE tb /*--结果 col1 col2 a 1,2 b 1,2,3 --*/ GO --3.3.2 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 GO --合并处理函数 CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(col2 as varchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,'')) END GO --调用函数 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
if object_id('[t2012]') is not null drop table [t2012] create table [t2012] (Goods varchar(3),Price numeric(3,2)) insert into [t2012] select 'AAA',0.1 union all select 'BBB',0.1 union all select 'CCC',0.2 union all select 'DDD',0.3 union all select 'FFF',0.1 union all select 'GGG',0.12 union all select 'HHH',0.2 union all select 'JJJ',0.3create function dbo.f_str2012(@Price numeric(3,2)) returns varchar(8000) as begin declare @r varchar(8000) set @r = '' select @r = @r + '-' + Goods from [t2012] where Price=@Price return stuff(@r, 1, 1, '') end go SELECt Goods = dbo.f_str2012(Price),Price FROM [t2012] GROUP BY Price /* Goods Price ------------------ --------------------------------------- AAA-BBB-FFF 0.10 GGG 0.12 CCC-HHH 0.20 DDD-JJJ 0.30 */
insert into tb select 'AAA',0.1
insert into tb select 'BBB',0.1
insert into tb select 'CCC',0.2
insert into tb select 'DDD',0.3
insert into tb select 'FFF',0.1
insert into tb select 'GGG',0.12
insert into tb select 'HHH',0.2
insert into tb select 'JJJ',0.3
go
select stuff((select ' '+Goods from tb where Price=a.Price for xml path('')),1,1,'')Goods,Price from tb a group by Price
/*
Goods Price
---------------------------- ---------------------------------------
AAA BBB FFF 0.10
GGG 0.12
CCC HHH 0.20
DDD JJJ 0.30(4 行受影响)*/
go
drop table tb
--合并列值
--原著:邹建
--改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳--表结构,数据如下:
/*
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
*/
--需要得到结果:
/*
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求value 的和(字符串相加)
*/
--1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + ',' + value from tb where id=@id
return stuff(@r, 1, 1, '')
end
go -- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id drop table tb
drop function dbo.f_str /*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为2 行)
*/ --2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go --创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go --调用自定义函数得到结果:
select distinct id ,dbo.f_hb(id) as value from tb drop table tb
drop function dbo.f_hb /*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为2 行)
*/ SQL SERVER 2000的话需要用自定义函数,2005+的话用晴天大大的就可以了。
我用的是 sql 2000
name num
aa 1
bb 2
cc 4
dd 4
ee 2
一条语句实现如下:
name
aa,bb,cc,dd,ee
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T
SELECT @STR
问题描述:
无论是在sql 2000,还是在sql 2005 中,都没有提供字符串的聚合函数,
所以,当我们在处理下列要求时,会比较麻烦:
有表tb, 如下:
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
1 aa,bb
2 aaa,bbb,ccc
即,group by id, 求value 的和(字符串相加)
1. 旧的解决方法
-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id
-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *FROM (SELECT DISTINCT Id FROM @t)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
( SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/
--各种字符串分函数
--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1 col2
a 1,2
b 1,2,3
--*/
GO
--3.3.2 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO
--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
if object_id('[t2012]') is not null drop table [t2012]
create table [t2012] (Goods varchar(3),Price numeric(3,2))
insert into [t2012]
select 'AAA',0.1 union all
select 'BBB',0.1 union all
select 'CCC',0.2 union all
select 'DDD',0.3 union all
select 'FFF',0.1 union all
select 'GGG',0.12 union all
select 'HHH',0.2 union all
select 'JJJ',0.3create function dbo.f_str2012(@Price numeric(3,2))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + '-' + Goods from [t2012] where Price=@Price
return stuff(@r, 1, 1, '')
end
go SELECt Goods = dbo.f_str2012(Price),Price FROM [t2012] GROUP BY Price
/*
Goods Price
------------------ ---------------------------------------
AAA-BBB-FFF 0.10
GGG 0.12
CCC-HHH 0.20
DDD-JJJ 0.30
*/