http://www.cnblogs.com/gaizai/archive/2010/10/20/1856314.html
解决方案 »
- 使用ODBC方式连接SQLServer数据库,如何修改连接的默认数据库?
- C# sqlserver2005 类似创建已有的表
- 动态组装的引号和加号的疑问
- 求一个sql语句~~~大侠拜托了。。。
- sql高手速进100分
- 求修改字段为自增类型的T-SQL语句
- 一条简单的SQL语句问题。
- 在asp中怎么样做sql数据库的临时表
- 从Access导入到SQL Server转换的奇怪问题!
- 请问informix7.0中的odbc用什么简体中文字符集????
- 用VC通过ADO修改字段数据成功,management studio 显示没改变?邪门了
- 本地单服务器有限存储空间下大量数据存储时如何维护数据库以保证其正常运行?
--*******************************************************************************************
表结构,数据如下:
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中只能用函数解决。)
--=============================================================================
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
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
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
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法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 select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb
;with t1 as
(
select id,colid,text from syscomments
where id in
(
select
object_id(name)
from dbo.sysobjects
where xtype='P'
)
and text like '%raiserror%'
),
t2 as
(
select id,colid,text from t1 where colid=1
union all
select t2.id, t2.colid, t1.text+';;'+t2.text
from t2 join t1 on t2.id=t1.id and t2.colid=t1.colid+1
),
t3 as
(
select id, max(colid) from t2 group by id
)
select t2.* from t2 join t3 on t2.id=t3.id and t2.colid=t3.colid
;with f as
(select id,colid,text from syscomments
where id in
(
select
object_id(name)
from dbo.sysobjects
where xtype='P'
)
and text like '%raiserror%')select id, [text]=stuff((select ','+[text] from f where id=t.id for xml path('')), 1, 1, '') from f as t group by id
;with f as
(select id,colid,text from syscomments
where id in
(
select
object_id(name)
from dbo.sysobjects
where xtype='P'
)
and text like '%raiserror%')select id, [text]=stuff((select ','+[text] from f where id=t.id for xml path('')), 1, 1, '') from f as t group by id
select id,REPLACE(text,'
',char(13))
from
(select ID,
(select ''+text from syscomments where id=t.id for xml path('') ) text
from syscomments t
where id in (select id from sysobjects where xtype='P')
group by id) t